Re: Query Performance / Planner estimate off

[Sebastian Dressler]

Hi Mats,

On 20. Oct 2020, at 11:37, Mats Julian Olsen 
mailto:m...@duneanalytics.com>> wrote:

[...]

1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR
2) enable_nestloop=off (4 min): https://explain.depesz.com/s/buKK
3) enable_nestloop=off; enable_seqscan=off (2 min): 
https://explain.depesz.com/s/0WXx

How can I get Postgres not to loop over 12M rows?

I looked at the plans and your config and there are some thoughts I'm having:

- The row estimate is off, as you possibly noticed. This can be possibly solved 
by raising `default_statistics_target` to e.g. 2500 (we typically use that) and 
run ANALYZE

- I however think that the misestimate might be caused by the evt_tx_hash being 
of type bytea. I believe that PG cannot estimate this very well for JOINs and 
will rather pick row numbers too low. Hence the nested loop is picked and there 
might be no way around this. I have experienced similar things when applying 
JOINs on VARCHAR with e.g. more than 3 fields for comparison.

- Other things to look into:

- work_mem seems too low to me with 56MB, consider raising this to the GB 
range to avoid disk-based operations
- min_parallel_table_scan_size - try 0
- parallel_setup_cost (default 1000, maybe try 500)
- parallel_tuple_cost (default 1.0, maybe try 0.1)
- random_page_cost (as mentioned consider raising this maybe much higher, 
factor 10 or sth like this) or (typically) seq_page_cost can be possibly much 
lower (0.1, 0.01) depending on your storage

I hope this helps to get to a parallel plan without setting `nested_loop = 
off`. EXPLAIN should be enough already to see the difference.

Best,
Sebastian

--

Sebastian Dressler, Solution Architect
+49 30 994 0496 72 | sebast...@swarm64.com

Swarm64 AS
Parkveien 41 B | 0258 Oslo | Norway
Registered at Brønnøysundregistrene in Norway under Org.-Number 911 662 787
CEO/Geschäftsführer (Daglig Leder): Thomas Richter; Chairman/Vorsitzender 
(Styrets Leder): Dr. Sverre Munck

Swarm64 AS Zweigstelle Hive
Ullsteinstr. 120 | 12109 Berlin | Germany
Registered at Amtsgericht Charlottenburg - HRB 154382 B

[cid:image001.jpg@01D5413F.54ADD080]

Re: Query Performance / Planner estimate off

[Mats Olsen]

On 10/21/20 2:38 PM, Sebastian Dressler wrote:

Hi Mats,

On 20. Oct 2020, at 11:37, Mats Julian Olsen > wrote:


[...]

1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR 

2) enable_nestloop=off (4 min): https://explain.depesz.com/s/buKK 

3) enable_nestloop=off; enable_seqscan=off (2 min): 
https://explain.depesz.com/s/0WXx 


How can I get Postgres not to loop over 12M rows?


I looked at the plans and your config and there are some thoughts I'm 
having:


- The row estimate is off, as you possibly noticed. This can be 
possibly solved by raising `default_statistics_target` to e.g. 2500 
(we typically use that) and run ANALYZE
I've `set default_statistics_target=2500` and ran analyze on both tables 
involved, unfortunately the plan is the same. The columns we use for 
joining here are hashes and we expect very few duplicates in the tables. 
Hence I think extended statistics (storing most common values and 
histogram bounds) aren't useful for this kind of data. Would you say the 
same thing?


- I however think that the misestimate might be caused by the 
evt_tx_hash being of type bytea. I believe that PG cannot estimate 
this very well for JOINs and will rather pick row numbers too low. 
Hence the nested loop is picked and there might be no way around this. 
I have experienced similar things when applying JOINs on VARCHAR with 
e.g. more than 3 fields for comparison.


This is very interesting, and I have never heard of issues with using 
`bytea` for joins. Our entire database is filled with them, as we deal 
with hashes of different lengths. In fact I would estimate that 60% of 
columns are bytea's. My intuition would say that it's better to store 
the hashes as byte arrays, rather than `text` fields as you can compare 
the raw bytes directly without encoding first?  Do you have any 
references for this?


Alternatively, since I know the length of the hashes in advance, I 
could've used `varchar(n)`, but I don't think there's any gains to be 
had in postgres by doing that? Something like `bytea(n)` would also have 
been interesting, had postgres been able to exploit that information.




- Other things to look into:

    - work_mem seems too low to me with 56MB, consider raising this to 
the GB range to avoid disk-based operations

    - min_parallel_table_scan_size - try 0
    - parallel_setup_cost (default 1000, maybe try 500)
    - parallel_tuple_cost (default 1.0, maybe try 0.1)
    - random_page_cost (as mentioned consider raising this maybe much 
higher, factor 10 or sth like this) or (typically) seq_page_cost can 
be possibly much lower (0.1, 0.01) depending on your storage


I've tried various settings of these parameters now, and unfortunately 
the only parameter that alters the query plan is the last one 
(random_page_cost), which also has the side effect of (almost) forcing 
sequential scans for most queries as far as I understand? Our storage is 
Google Cloud pd-ssd.


Thank you so much for you response, I'm looking forward to keep the 
discussion going.




I hope this helps to get to a parallel plan without setting 
`nested_loop = off`. EXPLAIN should be enough already to see the 
difference.


Best,
Sebastian

--

Sebastian Dressler, Solution Architect
+49 30 994 0496 72 | sebast...@swarm64.com 

Swarm64 AS
Parkveien 41 B | 0258 Oslo | Norway
Registered at Brønnøysundregistrene in Norway under Org.-Number 911 
662 787
CEO/Geschäftsführer (Daglig Leder): Thomas 
Richter; Chairman/Vorsitzender (Styrets Leder): Dr. Sverre Munck


Swarm64 AS Zweigstelle Hive
Ullsteinstr. 120 | 12109 Berlin | Germany
Registered at Amtsgericht Charlottenburg - HRB 154382 B



Best,

Mats

Re: Query Performance / Planner estimate off

[Michael Lewis]

On Wed, Oct 21, 2020, 8:42 AM Mats Olsen  wrote:

>
> On 10/21/20 2:38 PM, Sebastian Dressler wrote:
>
> Hi Mats,
>
> On 20. Oct 2020, at 11:37, Mats Julian Olsen 
> wrote:
>
> [...]
>
> 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR
> 2) enable_nestloop=off (4 min): https://explain.depesz.com/s/buKK
> 3) enable_nestloop=off; enable_seqscan=off (2 min):
> https://explain.depesz.com/s/0WXx
>
> How can I get Postgres not to loop over 12M rows?
>
>
> I looked at the plans and your config and there are some thoughts I'm
> having:
>
> - The row estimate is off, as you possibly noticed. This can be possibly
> solved by raising `default_statistics_target` to e.g. 2500 (we typically
> use that) and run ANALYZE
>
> I've `set default_statistics_target=2500` and ran analyze on both tables
> involved, unfortunately the plan is the same. The columns we use for
> joining here are hashes and we expect very few duplicates in the tables.
> Hence I think extended statistics (storing most common values and histogram
> bounds) aren't useful for this kind of data. Would you say the same thing?
>

Have you checked if ndistinct is roughly accurate? It can be set manually
on a column, or set to some value less than one with the calculation
depending on reltuples.

Re: Query Performance / Planner estimate off

[Sebastian Dressler]

Hi Mats,

Happy to help.

On 21. Oct 2020, at 16:42, Mats Olsen 
mailto:m...@duneanalytics.com>> wrote:
On 10/21/20 2:38 PM, Sebastian Dressler wrote:
Hi Mats,

On 20. Oct 2020, at 11:37, Mats Julian Olsen 
mailto:m...@duneanalytics.com>> wrote:

[...]

1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR
2) enable_nestloop=off (4 min): https://explain.depesz.com/s/buKK
3) enable_nestloop=off; enable_seqscan=off (2 min): 
https://explain.depesz.com/s/0WXx

How can I get Postgres not to loop over 12M rows?

I looked at the plans and your config and there are some thoughts I'm having:

- The row estimate is off, as you possibly noticed. This can be possibly solved 
by raising `default_statistics_target` to e.g. 2500 (we typically use that) and 
run ANALYZE
I've `set default_statistics_target=2500` and ran analyze on both tables 
involved, unfortunately the plan is the same. The columns we use for joining 
here are hashes and we expect very few duplicates in the tables. Hence I think 
extended statistics (storing most common values and histogram bounds) aren't 
useful for this kind of data. Would you say the same thing?

Yes, that looks like a given in this case.


- I however think that the misestimate might be caused by the evt_tx_hash being 
of type bytea. I believe that PG cannot estimate this very well for JOINs and 
will rather pick row numbers too low. Hence the nested loop is picked and there 
might be no way around this. I have experienced similar things when applying 
JOINs on VARCHAR with e.g. more than 3 fields for comparison.

This is very interesting, and I have never heard of issues with using `bytea` 
for joins. Our entire database is filled with them, as we deal with hashes of 
different lengths. In fact I would estimate that 60% of columns are bytea's. My 
intuition would say that it's better to store the hashes as byte arrays, rather 
than `text` fields as you can compare the raw bytes directly without encoding 
first?  Do you have any references for this?

Unfortunately, I have not dealt yet with `bytea` that much. It just rang a bell 
when I saw these kind of off-estimates in combination with nested loops. In the 
case I referenced it was, that the tables had 3 VARCHAR columns to be joined on 
and the estimate was very much off. As a result, PG chose nested loops in the 
upper layers of processing. Due to another JOIN the estimate went down to 1 row 
whereas it was 1 million rows in reality. Now, yours is "only" a factor 5 away, 
i.e. this might be a totally different reason.

However, I looked into the plan once more and realized, that the source of the 
problem could also be the scan on "Pair_evt_Mint" along the date dimension. 
Although you have a stats target of 10k there. If the timestamp is (roughly) 
sorted, you could try adding a BRIN index and by that maybe get a better 
estimate & scan-time.

Alternatively, since I know the length of the hashes in advance, I could've 
used `varchar(n)`, but I don't think there's any gains to be had in postgres by 
doing that? Something like `bytea(n)` would also have been interesting, had 
postgres been able to exploit that information.

I think giving VARCHAR a shot makes sense, maybe on an experimental basis to 
see whether the estimates get better. Maybe PG can then estimate that there are 
(almost) no dupes within the table but that there are N-many across tables. 
Another option to explore is maybe to use UUID as a type. As said above, it 
more looks like the timestamp causing the mis-estimate.

Maybe try querying this table by itself with that timestamp to see what kind of 
estimate you get?

- Other things to look into:

- work_mem seems too low to me with 56MB, consider raising this to the GB 
range to avoid disk-based operations
- min_parallel_table_scan_size - try 0
- parallel_setup_cost (default 1000, maybe try 500)
- parallel_tuple_cost (default 1.0, maybe try 0.1)
- random_page_cost (as mentioned consider raising this maybe much higher, 
factor 10 or sth like this) or (typically) seq_page_cost can be possibly much 
lower (0.1, 0.01) depending on your storage

I've tried various settings of these parameters now, and unfortunately the only 
parameter that alters the query plan is the last one (random_page_cost), which 
also has the side effect of (almost) forcing sequential scans for most queries 
as far as I understand? Our storage is Google Cloud pd-ssd.

I think a combination of random_page_cost with parallel_tuple_cost and 
min_parallel_table_scan_size might make sense. By that you possibly get at 
least parallel sequential scans. But I understand that this is possibly having 
the same effect as using `enable_nestloop = off`.

Thank you so much for you response, I'm looking forward to keep the discussion 
going.

You're very welcome.

Best,
Sebastian

--

Sebastian Dressler, Solution Architect
+49 30 994 0496 72 | sebast...@swarm64.com

Swarm64 AS
Parkveien 41 B | 0258 Oslo

Query performance

[Nagaraj Raj]

Hi, I have long running query which running for long time and its planner 
always performing sequnce scan the table2.My gole is to reduce Read IO on the 
disk cause, this query runns more oftenly ( using this in funtion for ETL). 

table1: transfer_order_header(records 2782678)table2: transfer_order_item ( 
records: 15995697)here is the query:

set work_mem = '688552kB';explain (analyze,buffers)select     
COALESCE(itm.serialnumber,'') AS SERIAL_NO,             
COALESCE(itm.ITEM_SKU,'') AS SKU,             COALESCE(itm.receivingplant,'') 
AS RECEIVINGPLANT,  COALESCE(itm.STO_ID,'') AS STO, supplyingplant,            
COALESCE(itm.deliveryitem,'') AS DELIVERYITEM,     min(eventtime) as eventtime  
FROM sor_t.transfer_order_header hed,sor_t.transfer_order_item itm  where 
hed.eventid=itm.eventid group by 1,2,3,4,5,6

Query Planner[2]:

"Finalize GroupAggregate (cost=1930380.06..4063262.11 rows=16004137 width=172) 
(actual time=56050.500..83268.566 rows=15891873 loops=1)"" Group Key: 
(COALESCE(itm.serialnumber, ''::character varying)), (COALESCE(itm.item_sku, 
''::character varying)), (COALESCE(itm.receivingplant, ''::character varying)), 
(COALESCE(itm.sto_id, ''::character varying)), hed.supplyingplant, 
(COALESCE(itm.deliveryitem, ''::character varying))"" Buffers: shared 
hit=712191 read=3, temp read=38232 written=38233"" -> Gather Merge 
(cost=1930380.06..3669827.09 rows=13336780 width=172) (actual 
time=56050.488..77106.993 rows=15948520 loops=1)"" Workers Planned: 2"" Workers 
Launched: 2"" Buffers: shared hit=2213081 read=12, temp read=142840 
written=142843"" -> Partial GroupAggregate (cost=1929380.04..2129431.74 
rows=6668390 width=172) (actual time=50031.458..54888.828 rows=5316173 
loops=3)"" Group Key: (COALESCE(itm.serialnumber, ''::character varying)), 
(COALESCE(itm.item_sku, ''::character varying)), (COALESCE(itm.receivingplant, 
''::character varying)), (COALESCE(itm.sto_id, ''::character varying)), 
hed.supplyingplant, (COALESCE(itm.deliveryitem, ''::character varying))"" 
Buffers: shared hit=2213081 read=12, temp read=142840 written=142843"" -> Sort 
(cost=1929380.04..1946051.01 rows=6668390 width=172) (actual 
time=50031.446..52823.352 rows=5332010 loops=3)"" Sort Key: 
(COALESCE(itm.serialnumber, ''::character varying)), (COALESCE(itm.item_sku, 
''::character varying)), (COALESCE(itm.receivingplant, ''::character varying)), 
(COALESCE(itm.sto_id, ''::character varying)), hed.supplyingplant, 
(COALESCE(itm.deliveryitem, ''::character varying))"" Sort Method: external 
merge Disk: 305856kB"" Worker 0: Sort Method: external merge Disk: 436816kB"" 
Worker 1: Sort Method: external merge Disk: 400048kB"" Buffers: shared 
hit=2213081 read=12, temp read=142840 written=142843"" -> Parallel Hash Join 
(cost=133229.66..603743.97 rows=6668390 width=172) (actual 
time=762.925..3901.133 rows=5332010 loops=3)"" Hash Cond: ((itm.eventid)::text 
= (hed.eventid)::text)"" Buffers: shared hit=2213027 read=12"" -> Parallel Seq 
Scan on transfer_order_item itm (cost=0.00..417722.90 rows=6668390 width=68) 
(actual time=0.005..524.359 rows=5332010 loops=3)"" Buffers: shared 
hit=351039"" -> Parallel Hash (cost=118545.68..118545.68 rows=1174718 width=35) 
(actual time=755.590..755.590 rows=926782 loops=3)"" Buckets: 4194304 Batches: 
1 Memory Usage: 243808kB"" Buffers: shared hit=1861964 read=12"" -> Parallel 
Index Only Scan using 
transfer_order_header_eventid_supplyingplant_eventtime_idx1 on 
transfer_order_header hed (cost=0.56..118545.68 rows=1174718 width=35) (actual 
time=0.128..388.436 rows=926782 loops=3)"" Heap Fetches: 18322"" Buffers: 
shared hit=1861964 read=12""Planning Time: 1.068 ms""Execution Time: 84274.004 
ms"

Tables[1]  created ddls in dbfiddle.



PG Server:  PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 
4.9.3, 64-bit.RAM: 456Mem Settings: "maintenance_work_mem" "8563712" "kB"
"work_mem" "688552" "kB"
"wal_buffers" "2048"  "8kB"
"shared_buffers" "44388442"     "8kB"


Any suggestions would greatly appretiated. 


Thanks,Rj

Re: Query performance

[Justin Pryzby]

On Thu, Oct 22, 2020 at 12:32:29AM +, Nagaraj Raj wrote:
> Hi, I have long running query which running for long time and its planner 
> always performing sequnce scan the table2.My gole is to reduce Read IO on the 
> disk cause, this query runns more oftenly ( using this in funtion for ETL). 
> 
> table1: transfer_order_header(records 2782678)table2: transfer_order_item ( 
> records: 15995697)here is the query:
> 
> set work_mem = '688552kB';explain (analyze,buffers)select     
> COALESCE(itm.serialnumber,'') AS SERIAL_NO,             
> COALESCE(itm.ITEM_SKU,'') AS SKU,             COALESCE(itm.receivingplant,'') 
> AS RECEIVINGPLANT,  COALESCE(itm.STO_ID,'') AS STO, supplyingplant,           
>  COALESCE(itm.deliveryitem,'') AS DELIVERYITEM,     min(eventtime) as 
> eventtime  FROM sor_t.transfer_order_header hed,sor_t.transfer_order_item itm 
>  where hed.eventid=itm.eventid group by 1,2,3,4,5,6

It spends most its time writing tempfiles for sorting, so it (still) seems to
be starved for work_mem.
|Sort (cost=1929380.04..1946051.01 rows=6668390 width=172) (actual 
time=50031.446..52823.352 rows=5332010 loops=3)

First, can you get a better plan with 2GB work_mem or with enable_sort=off ?  

If so, maybe you could make it less expensive by moving all the coalesce()
into a subquery, like
| SELECT COALESCE(a,''), COALESCE(b,''), .. FROM (SELECT a,b, .. GROUP BY 
1,2,..)x;

Or, if you have a faster disks available, use them for temp_tablespace.

-- 
Justin

Re: Query performance

[David G. Johnston]

On Wed, Oct 21, 2020 at 5:32 PM Nagaraj Raj  wrote:

> Hi, I have long running query which running for long time and its planner
> always performing sequnce scan the table2.
>

 FROM sor_t.transfer_order_header hed,sor_t.transfer_order_item itm
>  where hed.eventid=itm.eventid group by 1,2,3,4,5,6
>
> Any suggestions would greatly appretiated.
>

You aren't filtering out any rows so it is unsurprising that a sequential
scan was chosen to fulfil the request that the entire detail table be
consulted.  The good news is you have access to parallelism - see if you
can increase that factor.

Any other suggestions probably requires more knowledge of your problem
domain than you've provided here.

Finding a way to add a where clause or compute your desired result during
record insertion or updating are two other potential avenues of
consideration.

David J.

Re: CPU Consuming query. Sequential scan despite indexing.

[aditya desai]

Hi,
Kindly requesting an update on this. Thanks.

-Aditya.

On Tue, Oct 20, 2020 at 6:26 PM aditya desai  wrote:

>
>
> On Mon, Oct 19, 2020 at 9:50 PM Michael Lewis  wrote:
>
>> Reply to the group, not just me please. Btw, when you do reply to the
>> group, it is best practice on these lists to reply in-line and not just
>> reply on top with all prior messages quoted.
>>
>
> Hi  Michael,
> Please see below inline response. I tried all this on Dev env 2 vCPU and 8
> GB RAM. Still waiting for the PST environment :( with better configuration.
>
>>
>> On Sun, Oct 18, 2020 at 3:23 AM aditya desai  wrote:
>>
>>> I tried vacuum full and execution time came down to half.
>>>
>> Great to hear.
>>
>>
>>> However, it still consumes CPU. Setting parallel workers per gather to 0
>>> did not help much.
>>>
>> You didn't answer all of my questions, particularly about disabling
>> sequential scan. If you still have the default random_page_cost of 4, it
>> might be that 1.5 allows better estimates for cost on index (random) vs
>> sequential scan of a table.
>>
>
> Please see the next inline answer.
>
>>
>> Laurenz is a brilliant guy. I would implement the indexes he suggests if
>> you don't have them already and report back. If the indexes don't get used,
>> try set enable_seqscan = false; before the query and if it is way
>> faster, then reduce random_page_cost to maybe 1-2 depending how your
>> overall cache hit ratio is across the system.
>>
>
> Query plan with enable_seqscan=off , Random page cost=1. With this
> execution time and cost of query is almost less than half compared to
> original settings. Also used the suggestions given by Laurenze. 1. Made use
> of UINON operator and created indexes.
>
> lmp_delivery_jobs=> explain (analyze,buffers) with JobCount as ( (select
> jobstatuscode,count(1) stat_count from job j where 1=1 and j.countrycode =
> 'TH'   and j.facilitycode in
> ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1')
> and j.jobstartdatetime  between '2020-08-01 00:00:00' and '2020-09-30
> 00:00:00'group by j.jobstatuscode) UNION (select jobstatuscode,count(1)
> stat_count from job j where 1=1 and j.countrycode = 'TH'   and
> j.facilitycode in
> ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1')
> and j.jobstartdatetime is null  group by j.jobstatuscode))
>  select js.jobstatuscode,COALESCE(stat_count,0) stat_count from JobCount
> jc right outer join jobstatus js on jc.jobstatuscode=js.jobstatuscode;
>
>   QUERY PLAN
>
>
> 
> ---
>  Hash Right Join  (cost=68652.52..68652.76 rows=10 width=12) (actual
> time=676.477..676.495 rows=10 loops=1)
>Hash Cond: ((jc.jobstatuscode)::text = (js.jobstatuscode)::text)
>Buffers: shared hit=11897
>CTE jobcount
>  ->  HashAggregate  (cost=68650.01..68650.11 rows=10 width=24) (actual
> time=676.451..676.454 rows=8 loops=1)
>Group Key: j.jobstatuscode, (count(1))
>Buffers: shared hit=11895
>->  Append  (cost=68645.89..68649.96 rows=10 width=24) (actual
> time=676.346..676.441 rows=8 loops=1)
>  Buffers: shared hit=11895
>  ->  Finalize GroupAggregate  (cost=68645.89..68648.17
> rows=9 width=12) (actual time=676.345..676.379 rows=8 loops=1)
>Group Key: j.jobstatuscode
>Buffers: shared hit=11889
>->  Gather Merge  (cost=68645.89..68647.99 rows=18
> width=12) (actual time=676.330..676.403 rows=24 loops=1)
>  Workers Planned: 2
>  Workers Launched: 2
>  Buffers: shared hit=29067 read=1
>  I/O Timings: read=0.038
>  ->  Sort  (cost=67645.87..67645.89 rows=9
> width=12) (actual time=669.544..669.548 rows=8 loops=3)
>Sort Key: j.jobstatuscode
>Sort Method: quicksort  Memory: 25kB
>Worker 0:  Sort Method: quicksort
> Memory: 25kB
>Worker 1:  Sort Method: quicksort
> Memory: 25kB
>Buffers: shared hit=29067 read=1
>I/O Timings: read=0.038
>->  Partial HashAggregate
> (cost=67645.63..67645.72 rows=9 width=12) (actual time=669.506..669.511
> rows=8 loops=3)
>  Group Key: j.jobstatuscode
>  Buffers: shared hit=29051 read=1
>  I/O Timings: read=0.038
> 

Re: CPU Consuming query. Sequential scan despite indexing.

[aditya desai]

Hi,
Kindly requesting for help on this. Thanks.

-Aditya.



On Tue, Oct 20, 2020 at 6:00 PM aditya desai  wrote:

> Hi Laurenz,
> I created
>
> On Fri, Oct 16, 2020 at 2:06 PM Laurenz Albe 
> wrote:
>
>> On Thu, 2020-10-15 at 20:34 +0530, aditya desai wrote:
>> > Below query always shows up on top in the CPU matrix. Also despite
>> having indexes it does sequential scans
>> > (probably because WHERE condition satisfies almost all of the data from
>> table). This query
>> > runs on the default landing page in application and needs to fetch
>> records in less that 100 ms
>> >  without consuming too much CPU.
>> >
>> >  Any opinions? Table is very huge and due to referential identity and
>> business requirements we could not
>> >  implement partitioning as well.
>> >
>> > There is index on (countrycode,facilitycode,jobstartdatetime)
>> >
>> > explain (analyze,buffers) with JobCount as ( select
>> jobstatuscode,count(1) stat_count from job j
>> >  where 1=1 and j.countrycode = 'TH'
>> > and j.facilitycode in
>> ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1')
>> >  and ((j.jobstartdatetime  between '2020-08-01 00:00:00' and
>> '2020-09-30 00:00:00' ) or j.jobstartdatetime IS NULL )  group by
>> j.jobstatuscode)
>> >  select js.jobstatuscode,COALESCE(stat_count,0) stat_count from
>> JobCount jc right outer join jobstatus js on
>> jc.jobstatuscode=js.jobstatuscode;
>> >
>> >   QUERY PLAN
>> >
>> >  Hash Right Join  (cost=98845.93..98846.10 rows=10 width=12) (actual
>> time=1314.809..1314.849 rows=10 loops=1)
>> >  ->  Parallel Seq Scan on job j
>> (cost=0.00..96837.93 rows=200963 width=4) (actual time=13.010..1144.434
>> rows=163200 loops=3)
>> >Filter: (((countrycode)::text =
>> 'TH'::text) AND (((jobstartdatetime >= '2020-08-01 00:00:00'::timestamp
>> without time zone) AND (jobst
>> > artdatetime <= '2020-09-30 00:00:00'::timestamp without time zone)) OR
>> (jobstartdatetime IS NULL)) AND ((facilitycode)::text = ANY
>> ('{THNPM1,THPRK1,THCNT1,THSPN1,THKRI1
>> > ,THPKN1,THSBI1,THUTG1,THLRI1,THSRI1,THSUR1,THSKM1}'::text[])))
>> >Rows Removed by Filter: 449035
>> >Buffers: shared hit=60086 read=11834
>> >I/O Timings: read=59.194
>> >
>>
>> You should rewrite the subquery as a UNION to avoid the OR:
>>
>>   ... WHERE j.countrycode = 'TH'
>> and j.facilitycode in
>> ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1')
>> and j.jobstartdatetime between '2020-08-01 00:00:00' and
>> '2020-09-30 00:00:00'
>>
>> and
>>
>>   ... WHERE j.countrycode = 'TH'
>> and j.facilitycode in
>> ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1')
>> and j.jobstartdatetime IS NULL
>>
>> These indexes could speed up the resulting query:
>>
>>   CREATE INDEX ON job (countrycode, facilitycode);
>>   CREATE INDEX ON job (countrycode, jobstartdatetime);
>>   CREATE INDEX ON job (countrycode, facilitycode) WHERE jobstartdaytime
>> IS NULL;
>>
>
> I created the indexes you suggested and changed the query with the UNION
> operator. Please see explain plan below. Performance of the query(execution
> time has improved mostly because I ran vacuum full). Cost of the query is
> still high.This is Dev envrionment and has 2 vCPU and 8 GB RAM.
>
> explain (analyze,buffers) with JobCount as ( (select
> jobstatuscode,count(1) stat_count from job j where 1=1 and j.countrycode =
> 'TH'   and j.facilitycode in
> ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1')
> and j.jobstartdatetime  between '2020-08-01 00:00:00' and '2020-09-30
> 00:00:00'group by j.jobstatuscode) UNION (select jobstatuscode,count(1)
> stat_count from job j where 1=1 and j.countrycode = 'TH'   and
> j.facilitycode in
> ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1')
> and j.jobstartdatetime is null  group by j.jobstatuscode))
> lmp_delivery_jobs->  select js.jobstatuscode,COALESCE(stat_count,0)
> stat_count from JobCount jc right outer join jobstatus js on
> jc.jobstatuscode=js.jobstatuscode;
>
>  QUERY PLAN
>
>
> 
> -
>  Hash Right Join  (cost=79010.89..79011.19 rows=10 width=12) (actual
> time=444.241..444.256 rows=10 loops=1)
>Hash Cond: ((jc.jobstatuscode)::text = (js.jobstatuscode)::text)
>Buffers: shared hit=8560
>CTE jobcount
>  ->  HashAggregate  (cost=79002.35..79002.48 rows=

Re: CPU Consuming query. Sequential scan despite indexing.

[David G. Johnston]

On Wed, Oct 21, 2020 at 10:22 PM aditya desai  wrote:

> As per application team, it is business requirement to show last 60 days
>> worth data.
>>
>
I didn't look deeply but it sounds like you are looking backwards into 60
days worth of detail every single time you perform the query and computing
an aggregate directly from the detail.  Stop doing that.  By way of
example, at the end of every day compute the aggregates on the relevant
dimensions and save them.  Then query the saved aggregates from previous
days and add them to the computed aggregate from the current day's detail.

David J.

Re: CPU Consuming query. Sequential scan despite indexing.

[aditya desai]

Hi David,
Thanks for the suggestion. Let me try to implement this as well. WIll get
back to you soon.

Regards,
Aditya.

On Thu, Oct 22, 2020 at 11:03 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Oct 21, 2020 at 10:22 PM aditya desai  wrote:
>
>> As per application team, it is business requirement to show last 60 days
>>> worth data.
>>>
>>
> I didn't look deeply but it sounds like you are looking backwards into 60
> days worth of detail every single time you perform the query and computing
> an aggregate directly from the detail.  Stop doing that.  By way of
> example, at the end of every day compute the aggregates on the relevant
> dimensions and save them.  Then query the saved aggregates from previous
> days and add them to the computed aggregate from the current day's detail.
>
> David J.
>
>

Re: Query Performance / Planner estimate off

[Mats Olsen]

On 10/21/20 5:29 PM, Michael Lewis wrote:



On Wed, Oct 21, 2020, 8:42 AM Mats Olsen > wrote:



On 10/21/20 2:38 PM, Sebastian Dressler wrote:

Hi Mats,


On 20. Oct 2020, at 11:37, Mats Julian Olsen
mailto:m...@duneanalytics.com>> wrote:

[...]

1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR

2) enable_nestloop=off (4 min):
https://explain.depesz.com/s/buKK

3) enable_nestloop=off; enable_seqscan=off (2 min):
https://explain.depesz.com/s/0WXx


How can I get Postgres not to loop over 12M rows?


I looked at the plans and your config and there are some thoughts
I'm having:

- The row estimate is off, as you possibly noticed. This can be
possibly solved by raising `default_statistics_target` to e.g.
2500 (we typically use that) and run ANALYZE

I've `set default_statistics_target=2500` and ran analyze on both
tables involved, unfortunately the plan is the same. The columns
we use for joining here are hashes and we expect very few
duplicates in the tables. Hence I think extended statistics
(storing most common values and histogram bounds) aren't useful
for this kind of data. Would you say the same thing?


Have you checked if ndistinct is roughly accurate? It can be set 
manually on a column, or set to some value less than one with the 
calculation depending on reltuples.

Thank you for your reply!

I included ndistinct-counts in the gist: see 
https://gist.githubusercontent.com/mewwts/9f11ae5e6a5951593b8999559f5418cf/raw/24ca1f227940b48842a03435b731f82364f3576d/stats%2520Mint 
and 
https://gist.githubusercontent.com/mewwts/9f11ae5e6a5951593b8999559f5418cf/raw/24ca1f227940b48842a03435b731f82364f3576d/stats%2520transactions.


The join keys `transactions.hash` (unique) and 
`"Pair_evt_Mint".evt_tx_hash` (nearly unique) both have ndistinct=-1 
which seems to make sense to me. The Mint-table has -0.8375 for 
evt_block_time whereas this query returns 0.56 `select count(distinct 
evt_block_time)::numeric/count(*) from uniswap_v2."Pair_evt_Mint";`. 
Should I adjust that one?


Many of the other ndistinct-values for `transactions` seem strange, as 
it's a giant (partitioned) table, but I don't know enough about the 
statistics to draw any conclusions from it. What do you think?

Re: Query Performance / Planner estimate off

[Justin Pryzby]

On Wed, Oct 21, 2020 at 04:42:02PM +0200, Mats Olsen wrote:
> On 10/21/20 2:38 PM, Sebastian Dressler wrote:
> > > On 20. Oct 2020, at 11:37, Mats Julian Olsen  > > 
> > > [...]
> > > 
> > > 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR
> > > 
> > > 2) enable_nestloop=off (4 min): https://explain.depesz.com/s/buKK
> > > 
> > > 3) enable_nestloop=off; enable_seqscan=off (2 min):
> > > https://explain.depesz.com/s/0WXx
> > > 
> > > 
> > > How can I get Postgres not to loop over 12M rows?
> > 
> > I looked at the plans and your config and there are some thoughts I'm
> > having:
> > 
> > - The row estimate is off, as you possibly noticed. This can be possibly
> > solved by raising `default_statistics_target` to e.g. 2500 (we typically
> > use that) and run ANALYZE
> I've `set default_statistics_target=2500` and ran analyze on both tables
> involved, unfortunately the plan is the same. The columns we use for joining
> here are hashes and we expect very few duplicates in the tables. Hence I
> think extended statistics (storing most common values and histogram bounds)
> aren't useful for this kind of data. Would you say the same thing?

In postgres, extended statistics means "MV stats objects", not MCV+histogram,
which are "simple statistics", like ndistinct.

Your indexes maybe aren't ideal for this query, as mentioned.
The indexes that do exist might also be inefficient, due to being unclustered,
or bloated, or due to multiple columns.

These look redundant (which doesn't matter for this the query):

Partition key: RANGE (block_number)
Indexes:
"transactions_block_number_btree" btree (block_number DESC)
"transactions_block_number_hash_key" UNIQUE CONSTRAINT, btree 
(block_number, hash)
"transactions_block_number_time" btree (hash, block_number)

Maybe that would be an index just on "hash", which might help here.

Possibly you'd want to try to use a BRIN index on timestamp (or maybe
block_number?).

Maybe you'd want to VACUUM the table to allow index-only scan on the hash
columns ?

Maybe you'd want to check if reindexing reduces the index size ?  We don't know
if the table gets lots of UPDATE/DELETE or if any of the columns have high
logical vs physical "correlation".
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram

Have you ANALYZED the partitioned parent recently ?
This isn't handled by autovacuum.

-- 
Justin