I know this problem crops up all the time and I have read what I could
find, but I'm still not finding an answer to my problem. This is all
postgres 8.3. Yes, I've enabled constraint_exclusion. Yes, there are
indexes on the partitions, not just on the parent.
I've got a table with 1 month partitions. As it happens, I've only
got 2 partitions at the moment, one with 12 million rows and the other
with 5 million. I only discovered all of the caveats surrounding
indexes and partitioned tables when I executed a very simple query,
saw that it took far too long to run, and started looking at what the
query planner did. In this case, I simply want the set of distinct
values for a particular column, across all partitions. The set of
distinct values is very small (3) and there is an index on the column,
so I'd expect an index scan to return the 3 values almost
instantaneously. I turns out that when I query the partitions
directly, the planner does an index scan. When I query the parent
table, I get full table scans instead of merged output from n index
scans. Even worse, instead of getting the distinct values from each
partition and merging those, it merges each partition in its entirety
and then sorts and uniques, which is pretty much the pathological
execution order.
I'll give the queries, then the schema, then the various explain outputs.
(parent table) select distinct probe_type_num from
day_scale_radar_performance_fact; (30 seconds)
(partition) select distinct probe_type_num from
day_scale_radar_performace_fact_20100301_0000; (6 seconds)
(partition) select distinct probe_type_num from
day_scale_radar_performance_fact_20100401_0000; (1 second)
(manual union) select distinct probe_type_num from (select distinct
probe_type_num from day_scale_radar_performace_fact_20100301_0000
UNION select distinct probe_type_num from
day_scale_radar_performace_fact_20100401_0000) t2; (7 seconds)
In part, I'm surprised that the index scan takes as long as it does,
since I'd think an index would be able to return the set of keys
relatively quickly. But that's a secondary issue.
Parent table:
cedexis_v2=# \d day_scale_radar_performance_fact;
Table "perf_reporting.day_scale_radar_performance_fact"
Column | Type | Modifiers
----------------------------+-----------------------------+-----------
count | bigint | not null
total_ms | bigint | not null
time | timestamp without time zone | not null
market_num | integer | not null
country_num | integer | not null
autosys_num | integer | not null
provider_owner_zone_id | integer | not null
provider_owner_customer_id | integer | not null
provider_id | integer | not null
probe_type_num | integer | not null
Indexes:
"temp1_probe_type_num" btree (probe_type_num)
partition:
cedexis_v2=# \d day_scale_radar_performance_fact_20100301_0000;
Table "perf_reporting.day_scale_radar_performance_fact_20100301_0000"
Column | Type | Modifiers
----------------------------+-----------------------------+-----------
count | bigint | not null
total_ms | bigint | not null
time | timestamp without time zone | not null
market_num | integer | not null
country_num | integer | not null
autosys_num | integer | not null
provider_owner_zone_id | integer | not null
provider_owner_customer_id | integer | not null
provider_id | integer | not null
probe_type_num | integer | not null
Indexes:
"day_scale_radar_performance_fact_20100301_0000_asn" btree (autosys_num)
"day_scale_radar_performance_fact_20100301_0000_cty" btree (country_num)
"day_scale_radar_performance_fact_20100301_0000_mkt" btree (market_num)
"day_scale_radar_performance_fact_20100301_0000_p" btree (provider_id)
"day_scale_radar_performance_fact_20100301_0000_poc" btree
(provider_owner_customer_id)
"day_scale_radar_performance_fact_20100301_0000_poz" btree
(provider_owner_zone_id)
"day_scale_radar_performance_fact_20100301_0000_pt" btree (probe_type_num)
"day_scale_radar_performance_fact_20100301_0000_time" btree ("time")
Check constraints:
"day_scale_radar_performance_fact_20100301_0000_time_check" CHECK
("time" >= '2010-03-01 00:00:00'::timestamp without time zone AND
"time" < '2010-04-01 00:00:00'::timestamp without time zone)
Inherits: day_scale_radar_performance_fact
I also tried creating an index on the relevant column in the parent
table, but it had no effect, either way. You can see it in the table
description above
cedexis_v2=# explain select distinct probe_type_num from
day_scale_radar_performance_fact;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1864962.35..1926416.31 rows=200 width=4)
-> Sort (cost=1864962.35..1895689.33 rows=12290793 width=4)
Sort Key:
perf_reporting.day_scale_radar_performance_fact.probe_type_num
-> Result (cost=0.00..249616.93 rows=12290793 width=4)
-> Append (cost=0.00..249616.93 rows=12290793 width=4)
-> Seq Scan on day_scale_radar_performance_fact
(cost=0.00..19.90 rows=990 width=4)
-> Seq Scan on
day_scale_radar_performance_fact_20100401_0000
day_scale_radar_performance_fact (cost=0.00..31388.01 rows=1545501
width=4)
-> Seq Scan on
day_scale_radar_performance_fact_20100301_0000
day_scale_radar_performance_fact (cost=0.00..218209.02 rows=10744302
width=4)
cedexis_v2=# explain select distinct probe_type_num from
day_scale_radar_performance_fact_20100301_0000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.00..684328.92 rows=3 width=4)
-> Index Scan using
day_scale_radar_performance_fact_20100301_0000_pt on
day_scale_radar_performance_fact_20100301_0000 (cost=0.00..657468.16
rows=10744302 width=4)
And this is a lot closer to what I would hope the query planner would do:
cedexis_v2=# explain select distinct probe_type_num from (select
distinct probe_type_num from
day_scale_radar_performance_fact_20100401_0000 union
select distinct probe_type_num from
day_scale_radar_performance_fact_20100301_0000) t2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=781113.73..781113.84 rows=6 width=4)
-> Unique (cost=781113.73..781113.76 rows=6 width=4)
-> Sort (cost=781113.73..781113.75 rows=6 width=4)
Sort Key:
day_scale_radar_performance_fact_20100401_0000.probe_type_num
-> Append (cost=0.00..781113.66 rows=6 width=4)
-> Unique (cost=0.00..96784.68 rows=3 width=4)
-> Index Scan using
day_scale_radar_performance_fact_20100401_0000_pt on
day_scale_radar_performance_fact_20100401_0000 (cost=0.00..92920.93
rows=1545501 width=4)
-> Unique (cost=0.00..684328.92 rows=3 width=4)
-> Index Scan using
day_scale_radar_performance_fact_20100301_0000_pt on
day_scale_radar_performance_fact_20100301_0000 (cost=0.00..657468.16
rows=10744302 width=4)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
