[PERFORM] Huge overestimation in rows expected results in bad plan
Hi, I have a query that is getting a pretty bad plan due to a massively incorrect count of expected rows. All tables in the query were vacuum analyzed right before the query was tested. Disabling nested loops gives a significantly faster result (4s vs 292s). Any thoughts on what I can change to make the planner generate a better plan? 32GB ram effective_cache_size = 16GB shared_buffers = 4GB random_page_cost = 1.5 default_statistics_target = 100 Note: for the tables in question, I tested default_statistics_target at 100, then also at 5000 to see if there was an improvement (none noted). select version(); version -- PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit explain analyze select c.id, c.transactionid, c.clickgenerated, c.confirmed, c.rejected, cr.rejectedreason from conversion c inner join conversionrejected cr on cr.idconversion = c.id where date = '2010-11-06' and idaction = 12906 and idaffiliate = 198338 order by transactionid; QUERY PLAN --- Sort (cost=2318120.52..2345652.23 rows=11012683 width=78) (actual time=292668.896..292668.903 rows=70 loops=1) Sort Key: c.transactionid Sort Method: quicksort Memory: 43kB - Nested Loop (cost=1234.69..715468.13 rows=11012683 width=78) (actual time=8687.314..292668.159 rows=70 loops=1) Join Filter: ((cr.idconversion = c.id) OR (c.id = 38441828354::bigint)) - Append (cost=1234.69..1244.03 rows=2 width=56) (actual time=15.292..15.888 rows=72 loops=1) - Bitmap Heap Scan on conversion c (cost=1234.69..1240.76 rows=1 width=31) (actual time=15.291..15.840 rows=72 loops=1) Recheck Cond: ((idaffiliate = 198338) AND (date = '2010-11-06'::date)) Filter: (idaction = 12906) - BitmapAnd (cost=1234.69..1234.69 rows=4 width=0) (actual time=15.152..15.152 rows=0 loops=1) - Bitmap Index Scan on conversion_idaffiliate_idx (cost=0.00..49.16 rows=3492 width=0) (actual time=4.071..4.071 rows=28844 loops=1) Index Cond: (idaffiliate = 198338) - Bitmap Index Scan on conversion_date_idx (cost=0.00..1185.28 rows=79282 width=0) (actual time=10.343..10.343 rows=82400 loops=1) Index Cond: (date = '2010-11-06'::date) - Index Scan using conversionlate_date_idx on conversionlate c (cost=0.00..3.27 rows=1 width=80) (actual time=0.005..0.005 rows=0 loops=1) Index Cond: (date = '2010-11-06'::date) Filter: ((idaction = 12906) AND (idaffiliate = 198338)) - Seq Scan on conversionrejected cr (cost=0.00..191921.82 rows=11012682 width=31) (actual time=0.003..1515.816 rows=11012682 loops=72) Total runtime: 292668.992 ms select count(*) from conversionrejected ; count -- 11013488 Time: 3649.647 ms select count(*) from conversion where date = '2010-11-06'; count --- 82400 Time: 507.985 ms select count(*) from conversion; count -- 73419376(1 row) Time: 7100.619 ms -- with enable_nestloop to off; -- much faster! QUERY PLAN - Sort (cost=234463.54..234463.54 rows=2 width=78) (actual time=4035.340..4035.347 rows=70 loops=1) Sort Key: c.transactionid Sort Method: quicksort Memory: 43kB - Hash Join (cost=1244.13..234463.53 rows=2 width=78) (actual time=4024.816..4034.715 rows=70 loops=1) Hash Cond: (cr.idconversion = c.id) - Seq Scan on conversionrejected cr (cost=0.00..191921.82 rows=11012682 width=31) (actual time=0.003..1949.597 rows=11013576 loops=1) - Hash (cost=1244.11..1244.11 rows=2 width=56) (actual time=19.312..19.312 rows=72 loops=1) - Append (cost=1234.77..1244.11 rows=2 width=56) (actual time=18.539..19.261 rows=72 loops=1) - Bitmap Heap Scan on conversion c (cost=1234.77..1240.83 rows=1 width=31) (actual time=18.538..19.235 rows=72 loops=1) Recheck Cond: ((idaffiliate = 198338) AND (date = '2010-11-06'::date)) Filter: (idaction = 12906) - BitmapAnd (cost=1234.77..1234.77 rows=4 width=0) (actual time=18.237..18.237 rows=0 loops=1) - Bitmap Index Scan on conversion_idaffiliate_idx (cost=0.00..49.16 rows=3492 width=0) (actual time=4.932..4.932 rows=28844 loops=1)
Re: [PERFORM] Huge overestimation in rows expected results in bad plan
On 11/9/2010 3:26 PM, bricklen wrote: Hi, I have a query that is getting a pretty bad plan due to a massively incorrect count of expected rows. All tables in the query were vacuum analyzed right before the query was tested. Disabling nested loops gives a significantly faster result (4s vs 292s). Any thoughts on what I can change to make the planner generate a better plan? explain analyze select c.id, c.transactionid, c.clickgenerated, c.confirmed, c.rejected, cr.rejectedreason from conversion c inner join conversionrejected cr on cr.idconversion = c.id where date = '2010-11-06' and idaction = 12906 and idaffiliate = 198338 order by transactionid; - Seq Scan on conversionrejected cr (cost=0.00..191921.82 rows=11012682 width=31) (actual time=0.003..1515.816 rows=11012682 loops=72) Total runtime: 292668.992 ms Looks like the table stats are ok. But its doing a sequential scan. Are you missing an index? Also: http://explain.depesz.com/ is magic. -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Huge overestimation in rows expected results in bad plan
On Tue, Nov 9, 2010 at 2:48 PM, Andy Colson a...@squeakycode.net wrote: On 11/9/2010 3:26 PM, bricklen wrote: - Seq Scan on conversionrejected cr (cost=0.00..191921.82 rows=11012682 width=31) (actual time=0.003..1515.816 rows=11012682 loops=72) Total runtime: 292668.992 ms Looks like the table stats are ok. But its doing a sequential scan. Are you missing an index? Also: http://explain.depesz.com/ is magic. -Andy The PK is on the conversionrejected table in all three databases I tested (I also tested our Greenplum datawarehouse). The idconversion attribute is a bigint in both tables, so it's not a type mismatch. \d conversionrejected Table public.conversionrejected Column | Type | Modifiers ++--- idconversion | bigint | not null rejectedreason | text | not null Indexes: conversionrejected_pk PRIMARY KEY, btree (idconversion) Yeah, that explain visualizer from depesz is a handy tool, I use frequently. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Huge overestimation in rows expected results in bad plan
bricklen brick...@gmail.com writes: I have a query that is getting a pretty bad plan due to a massively incorrect count of expected rows. The query doesn't seem to match the plan. Where is that OR (c.id = 38441828354::bigint) condition coming from? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Huge overestimation in rows expected results in bad plan
On Tue, Nov 9, 2010 at 3:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: bricklen brick...@gmail.com writes: I have a query that is getting a pretty bad plan due to a massively incorrect count of expected rows. The query doesn't seem to match the plan. Where is that OR (c.id = 38441828354::bigint) condition coming from? regards, tom lane Ah sorry, I was testing it with and without that part. Here is the corrected query, with that as part of the join condition: explain analyze select c.id, c.transactionid, c.clickgenerated, c.confirmed, c.rejected, cr.rejectedreason from conversion c inner join conversionrejected cr on cr.idconversion = c.id or c.id = 38441828354 where date = '2010-11-06' and idaction = 12906 and idaffiliate = 198338 order by transactionid; -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Huge overestimation in rows expected results in bad plan
bricklen brick...@gmail.com writes: On Tue, Nov 9, 2010 at 3:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: The query doesn't seem to match the plan. Â Where is that OR (c.id = 38441828354::bigint) condition coming from? Ah sorry, I was testing it with and without that part. Here is the corrected query, with that as part of the join condition: explain analyze select c.id, c.transactionid, c.clickgenerated, c.confirmed, c.rejected, cr.rejectedreason from conversion c inner join conversionrejected cr on cr.idconversion = c.id or c.id = 38441828354 where date = '2010-11-06' and idaction = 12906 and idaffiliate = 198338 order by transactionid; Hm. Well, the trouble with that query is that if there is any conversion row with c.id = 38441828354, it will join to *every* row of conversionrejected. The planner not unreasonably assumes there will be at least one such row, so it comes up with a join size estimate that's = size of conversionrejected; and it also tends to favor a seqscan since it thinks it's going to have to visit every row of conversionrejected anyway. If you have reason to think the c.id = 38441828354 test is usually dead code, you might see if you can get rid of it, or at least rearrange the query as a UNION of two independent joins. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Huge overestimation in rows expected results in bad plan
On Tue, Nov 9, 2010 at 3:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: bricklen brick...@gmail.com writes: On Tue, Nov 9, 2010 at 3:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: The query doesn't seem to match the plan. Where is that OR (c.id = 38441828354::bigint) condition coming from? Ah sorry, I was testing it with and without that part. Here is the corrected query, with that as part of the join condition: explain analyze select c.id, c.transactionid, c.clickgenerated, c.confirmed, c.rejected, cr.rejectedreason from conversion c inner join conversionrejected cr on cr.idconversion = c.id or c.id = 38441828354 where date = '2010-11-06' and idaction = 12906 and idaffiliate = 198338 order by transactionid; Hm. Well, the trouble with that query is that if there is any conversion row with c.id = 38441828354, it will join to *every* row of conversionrejected. The planner not unreasonably assumes there will be at least one such row, so it comes up with a join size estimate that's = size of conversionrejected; and it also tends to favor a seqscan since it thinks it's going to have to visit every row of conversionrejected anyway. If you have reason to think the c.id = 38441828354 test is usually dead code, you might see if you can get rid of it, or at least rearrange the query as a UNION of two independent joins. regards, tom lane Okay, thanks. I'll talk to the developer that wrote that query and see what he has to say about it. Cheers, Bricklen -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
