On PostgreSQL 9.1.1, I'm experiencing extremely slow/inefficient min/max
queries against a partitioned table, despite the recent improvements made in
version 9.1. I haven't seen this issue discussed since 9.1 was released, so I
wanted to provide an example of the inefficient execution plan in case this is
not a known issue with the new version.
In my case, the query analyzer chooses the wrong index to scan of the child
table when the query is made against the parent table. The tables are
partitioned by 'fctid'. The query 'SELECT max(date) FROM table WHERE fctid=301
and sec_id=1' correctly uses the index (sec_id, date) when querying against the
child table (0.1ms), but when run against the parent table, the planner chooses
to scan the (date, sec_id) primary key instead, resulting in a full table scan
in some instances (49 minutes!).
In my example the parent case is empty and all child tables have
non-overlapping check constraints. Below is the schema and execution plans.
Let me know if you need anything else. Thanks, Robert
Parent table schema:
template1=# \d f_data
Table "public.f_data"
Column | Type | Modifiers
--------+----------+-----------
sec_id | integer | not null
date | date | not null
fctid | smallint | not null
value | real | not null
Indexes:
"f_data_pkey" PRIMARY KEY, btree (fctid, date, sec_id)
Triggers:
insert_f_data_trigger BEFORE INSERT ON f_data FOR EACH ROW EXECUTE
PROCEDURE f_data_insert_trigger()
Number of child tables: 7 (Use \d+ to list them.)
Child table schema:
template1=# \d f_data301
Table "public.f_data301"
Column | Type | Modifiers
--------+----------+-----------
sec_id | integer | not null
date | date | not null
fctid | smallint | not null
value | real | not null
Indexes:
"pk_f_data_rsi2" PRIMARY KEY, btree (date, sec_id) CLUSTER
"f_data_rsi2_idx" btree (sec_id, date)
Check constraints:
"f_data_rsi2_fctid_check" CHECK (fctid = 301)
Inherits: f_data
template1=# EXPLAIN ANALYZE SELECT max(date) FROM f_data301 WHERE fctid=301 and
sec_id=1;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------
Result (cost=1.84..1.85 rows=1 width=0) (actual time=0.077..0.078 rows=1
loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..1.84 rows=1 width=4) (actual time=0.074..0.074
rows=0 loops=1)
-> Index Scan Backward using f_data_rsi2_idx on f_data301
(cost=0.00..6370.59 rows=3465 width=4) (a
Index Cond: ((sec_id = 1) AND (date IS NOT NULL))
Filter: (fctid = 301)
Total runtime: 0.132 ms
(7 rows)
template1=# EXPLAIN ANALYZE SELECT max(date) FROM f_data where fctid=301 and
sec_id=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Result (cost=522.10..522.11 rows=1 width=0) (actual
time=2921439.560..2921439.561 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.02..522.10 rows=1 width=4) (actual
time=2921439.554..2921439.554 rows=0 loops=1)
-> Merge Append (cost=0.02..1809543.34 rows=3466 width=4) (actual
time=2921439.551..2921439.551 row
Sort Key: public.f_data.date
-> Sort (cost=0.01..0.02 rows=1 width=4) (actual
time=0.008..0.008 rows=0 loops=1)
Sort Key: public.f_data.date
Sort Method: quicksort Memory: 25kB
-> Seq Scan on f_data (cost=0.00..0.00 rows=1 width=4)
(actual time=0.002..0.002 rows=0
Filter: ((date IS NOT NULL) AND (fctid = 301) AND
(sec_id = 1))
-> Index Scan Backward using pk_f_data_rsi2 on f_data301
f_data (cost=0.00..1809499.99 rows=3
Index Cond: ((date IS NOT NULL) AND (sec_id = 1))
Filter: (fctid = 301)
Total runtime: 2921439.645 ms
(14 rows)
template1=# select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-50), 64-bit
Robert McGehee, CFA
Geode Capital Management, LLC
One Post Office Square, 28th Floor | Boston, MA | 02109
Direct: (617)392-8396
This e-mail, and any attachments hereto, are intended for use by the
addressee(s) only and may contain information that is (i) confidential
information of Geode Capital Management, LLC and/or its affiliates, and/or (ii)
proprietary information of Geode Capital Management, LLC and/or its affiliates.
If you are not the intended recipient of this e-mail, or if you have otherwise
received this e-mail in error, please immediately notify me by telephone (you
may call collect), or by e-mail, and please permanently delete the original,
any print outs and any copies of the foregoing. Any dissemination, distribution
or copying of this e-mail is strictly prohibited.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
