Обсуждение: 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) Index Cond: (idaffiliate = 198338) -> Bitmap Index Scan on conversion_date_idx (cost=0.00..1185.36 rows=79292 width=0) (actual time=12.473..12.473 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.006..0.006 rows=0 loops=1) Index Cond: (date = '2010-11-06'::date) Filter: ((idaction = 12906) AND (idaffiliate = 198338)) Total runtime: 4035.439 ms -- for completeness, -- same query, on 9.0.0, underpowered server, 2 disks mirrored. Approximately the same table sizes/counts. select version(); version ------------------------------------------------------------------------------------------------------------------- PostgreSQL 9.0.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=120727.25..120727.25 rows=2 width=78) (actual time=3214.827..3214.867 rows=70 loops=1) Sort Key: c.transactionid Sort Method: quicksort Memory: 43kB -> Nested Loop (cost=697.95..120727.24 rows=2 width=78) (actual time=2955.321..3214.208 rows=70 loops=1) -> Append (cost=697.95..120712.87 rows=2 width=56) (actual time=2931.584..3173.402 rows=72 loops=1) -> Bitmap Heap Scan on conversion c (cost=697.95..120706.59 rows=1 width=31) (actual time=2931.582..3150.231 rows=72 loops=1) Recheck Cond: (date = '2010-11-06'::date) Filter: ((idaction = 12906) AND (idaffiliate = 198338)) -> Bitmap Index Scan on conversion_date_idx (cost=0.00..697.95 rows=44365 width=0) (actual time=51.692..51.692 rows=82400 loops=1) Index Cond: (date = '2010-11-06'::date) -> Index Scan using conversionlate_idaffiliate_idx on conversionlate c (cost=0.00..6.27 rows=1 width=80) (actual time=23.091..23.091 rows=0 loops=1) Index Cond: (idaffiliate = 198338) Filter: ((date = '2010-11-06'::date) AND (idaction = 12906)) -> Index Scan using conversionrejected_pk on conversionrejected cr (cost=0.00..7.17 rows=1 width=31) (actual time=0.563..0.564 rows=1 loops=72) Index Cond: (cr.idconversion = c.id) Total runtime: 3214.972 ms Thanks, Bricklen
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
On Tue, Nov 9, 2010 at 2:48 PM, Andy Colson <andy@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.
bricklen <bricklen@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
On Tue, Nov 9, 2010 at 3:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > bricklen <bricklen@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;
bricklen <bricklen@gmail.com> writes: > On Tue, Nov 9, 2010 at 3:29 PM, Tom Lane <tgl@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
On Tue, Nov 9, 2010 at 3:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > bricklen <bricklen@gmail.com> writes: >> On Tue, Nov 9, 2010 at 3:29 PM, Tom Lane <tgl@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