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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
