Re: Slow select performance despite seemingly reasonable query plan - Mailing list pgsql-performance
| From | Nikolas Everett |
|---|---|
| Subject | Re: Slow select performance despite seemingly reasonable query plan |
| Date | |
| Msg-id | d4e11e980905070818o2f11ac95x48f3ecd829e0cc72@mail.gmail.com Whole thread Raw |
| In response to | Slow select performance despite seemingly reasonable query plan (David Brain <dbrain@bandwidth.com>) |
| List | pgsql-performance |
Nested Loop Left Join (cost=0.00..6462463.96 rows=1894 width=110)
-> Append (cost=0.00..6453365.66 rows=1894 width=118)
-> Seq Scan on datatable sum (cost=0.00..10.75 rows=1 width=118)
Filter: ((datapointdate >= '2009-04-01
00:00:00'::timestamp without time zone) AND (datapointdate <=
'2009-04-30 23:59:59'::timestamp without time zone) AND
((customerid)::text = 'xxxx'::text) AND (NOT CASE WHEN (NOT obsolete)
THEN false ELSE CASE WHEN (obsoletereasonid IS NULL) THEN true WHEN
(obsoletereasonid = 1) THEN true WHEN (obsoletereasonid = 2) THEN true
WHEN (cdrdatasourceid = 1) THEN false ELSE true END END))
-> Index Scan using
datatable_20090328_customeriddatapointdate_idx on datatable_20090328
sum (cost=0.00..542433.51 rows=180 width=49)
Index Cond: ((datapointdate >= '2009-04-01
00:00:00'::timestamp without time zone) AND (datapointdate <=
'2009-04-30 23:59:59'::timestamp without time zone) AND
((customerid)::text = 'xxxx'::text))
Filter: (NOT CASE WHEN (NOT obsolete) THEN false ELSE
CASE WHEN (obsoletereasonid IS NULL) THEN true WHEN (obsoletereasonid
= 1) THEN true WHEN (obsoletereasonid = 2) THEN true WHEN
(cdrdatasourceid = 1) THEN false ELSE true END END)
-> Index Scan using
datatable_20090404_customeriddatapointdate_idx on datatable_20090404
sum (cost=0.00..1322098.74 rows=405 width=48)
Index Cond: ((datapointdate >= '2009-04-01
00:00:00'::timestamp without time zone) AND (datapointdate <=
'2009-04-30 23:59:59'::timestamp without time zone) AND
((customerid)::text = 'xxxx'::text))
Filter: (NOT CASE WHEN (NOT obsolete) THEN false ELSE
CASE WHEN (obsoletereasonid IS NULL) THEN true WHEN (obsoletereasonid
= 1) THEN true WHEN (obsoletereasonid = 2) THEN true WHEN
(cdrdatasourceid = 1) THEN false ELSE true END END)
-> Index Scan using
datatable_20090411_customeriddatapointdate_idx on datatable_20090411
sum (cost=0.00..1612744.29 rows=450 width=48)
Index Cond: ((datapointdate >= '2009-04-01
00:00:00'::timestamp without time zone) AND (datapointdate <=
'2009-04-30 23:59:59'::timestamp without time zone) AND
((customerid)::text = 'xxxx'::text))
Filter: (NOT CASE WHEN (NOT obsolete) THEN false ELSE
CASE WHEN (obsoletereasonid IS NULL) THEN true WHEN (obsoletereasonid
= 1) THEN true WHEN (obsoletereasonid = 2) THEN true WHEN
(cdrdatasourceid = 1) THEN false ELSE true END END)
-> Index Scan using
datatable_20090418_customeriddatapointdate_idx on datatable_20090418
sum (cost=0.00..1641913.58 rows=469 width=49)
Index Cond: ((datapointdate >= '2009-04-01
00:00:00'::timestamp without time zone) AND (datapointdate <=
'2009-04-30 23:59:59'::timestamp without time zone) AND
((customerid)::text = 'xxxx'::text))
Filter: (NOT CASE WHEN (NOT obsolete) THEN false ELSE
CASE WHEN (obsoletereasonid IS NULL) THEN true WHEN (obsoletereasonid
= 1) THEN true WHEN (obsoletereasonid = 2) THEN true WHEN
(cdrdatasourceid = 1) THEN false ELSE true END END)
-> Index Scan using
datatable_20090425_customeriddatapointdate_idx on datatable_20090425
sum (cost=0.00..1334164.80 rows=389 width=49)
Index Cond: ((datapointdate >= '2009-04-01
00:00:00'::timestamp without time zone) AND (datapointdate <=
'2009-04-30 23:59:59'::timestamp without time zone) AND
((customerid)::text = 'xxxx'::text))
Filter: (NOT CASE WHEN (NOT obsolete) THEN false ELSE
CASE WHEN (obsoletereasonid IS NULL) THEN true WHEN (obsoletereasonid
= 1) THEN true WHEN (obsoletereasonid = 2) THEN true WHEN
(cdrdatasourceid = 1) THEN false ELSE true END END)
-> Index Scan using pk_cdrextension on cdrextension ext
(cost=0.00..4.77 rows=1 width=8)
Index Cond: (sum.id = ext.datatableid)
Something doesn't look right. Why is it doing an index scan on datatable_20090404 when the constraint for that table puts it as entirely in the date range? Shouldn't it just seq scan the partition or use the partition's customerid index?
pgsql-performance by date: