pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInefficient min/max against partition (ver 9.1.1)
2+ messages / 2 participants
[nested] [flat]
* Inefficient min/max against partition (ver 9.1.1)
@ 2012-02-29 17:32 McGehee, Robert <Robert.McGehee@geodecapital.com>
0 siblings, 1 reply; 2+ messages in thread
From: McGehee, Robert @ 2012-02-29 17:32 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Inefficient min/max against partition (ver 9.1.1)
@ 2012-03-02 19:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: McGehee, Robert <Robert.McGehee@geodecapital.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2012-03-02 19:31 UTC (permalink / raw)
To: McGehee, Robert <Robert.McGehee@geodecapital.com>; +Cc: pgsql-performance
"McGehee, Robert" <Robert.McGehee@geodecapital.com> writes:
> 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.
Thanks for the report. I believe this will fix it:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ef03b34550e3577c4be3baa25b70787f56...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-03-02 19:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-29 17:32 Inefficient min/max against partition (ver 9.1.1) McGehee, Robert <Robert.McGehee@geodecapital.com>
2012-03-02 19:31 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox