partitioning max() sql not using index
От | Kevin Kempter |
---|---|
Тема | partitioning max() sql not using index |
Дата | |
Msg-id | 200909081453.18931.kevink@consistentstate.com обсуждение исходный текст |
Ответы |
Re: partitioning max() sql not using index
(Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>)
|
Список | pgsql-performance |
If I hit a partition table directly I get an index scan as expected:
explain select max(id) from pwreport.bigtab_2009_09;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.06..0.07 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.06 rows=1 width=8)
-> Index Scan Backward using bigtab_2009_09_pk on bigtab_2009_09 (cost=0.00..12403809.95 rows=205659919 width=8)
Filter: (id IS NOT NULL)
(5 rows)
However if I hit the base table I get a sequential scan on every partition as opposed to index scans:
explain select max(id) from pwreport.bigtab;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=27214318.67..27214318.68 rows=1 width=8)
-> Append (cost=0.00..24477298.53 rows=1094808053 width=8)
-> Seq Scan on bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_12 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_11 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_10 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_09 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_08 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_07 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_06 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_05 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_04 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_03 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_02 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_01 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_12 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_11 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_10 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_09 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_08 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_07 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_06 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_05 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_04 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_03 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_02 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2010_01 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_12 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_11 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_10 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_09 bigtab (cost=0.00..4599227.19 rows=205659919 width=8)
-> Seq Scan on bigtab_2009_07 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_06 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_05 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_04 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_03 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_02 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_01 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2008_12 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2008_11 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2008_10 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2008_09 bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2009_08 bigtab (cost=0.00..19877615.04 rows=889141504 width=8)
(43 rows)
Thoughts?
Thanks in advance...
В списке pgsql-performance по дате отправления:
Предыдущее
От: David FetterДата:
Сообщение: Re: [GENERAL] [sfpug] Statistics and PostgreSQL: Streaming Webcast tonight