Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 4816E63482D for ; Tue, 6 Apr 2010 19:40:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 91649-01 for ; Tue, 6 Apr 2010 22:40:16 +0000 (UTC) X-Greylist: delayed 00:39:21.037544 by SQLgrey-1.7.6 Received: from mail-yw0-f184.google.com (mail-yw0-f184.google.com [209.85.211.184]) by mail.postgresql.org (Postfix) with ESMTP id 9FF2363280A for ; Tue, 6 Apr 2010 19:40:16 -0300 (ADT) Received: by ywh14 with SMTP id 14so194570ywh.15 for ; Tue, 06 Apr 2010 15:40:15 -0700 (PDT) MIME-Version: 1.0 Received: by 10.231.152.131 with HTTP; Tue, 6 Apr 2010 14:37:14 -0700 (PDT) X-Originating-IP: [66.215.95.51] Date: Tue, 6 Apr 2010 14:37:14 -0700 Received: by 10.231.146.66 with SMTP id g2mr1200906ibv.60.1270589834926; Tue, 06 Apr 2010 14:37:14 -0700 (PDT) Message-ID: Subject: indexes in partitioned tables - again From: Samuel Gendler To: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.443 tagged_above=-10 required=5 tests=BAYES_00=-2.599, SUBJECT_FUZZY_TION=0.156 X-Spam-Level: X-Archive-Number: 201004/60 X-Sequence-Number: 38310 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.=A0 As it happens, I've only got 2 partitions at the moment, one with 12 million rows and the other with 5 million.=A0 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.=A0 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.=A0 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.=A0 But that's a secondary issue. Parent table: cedexis_v2=3D# \d day_scale_radar_performance_fact; =A0=A0=A0=A0=A0=A0 Table "perf_reporting.day_scale_radar_performance_fact" =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Column=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | Modi= fiers ----------------------------+-----------------------------+----------- =A0count=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | b= igint=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not = null =A0total_ms=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | bigint= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null =A0time=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |= timestamp without time zone | not null =A0market_num=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | integer=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null =A0country_num=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | integer=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null =A0autosys_num=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | integer=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null =A0provider_owner_zone_id=A0=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null =A0provider_owner_customer_id | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 | not null =A0provider_id=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | integer=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null =A0probe_type_num=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | integer=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null Indexes: =A0=A0=A0 "temp1_probe_type_num" btree (probe_type_num) partition: cedexis_v2=3D# \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_n= um) "day_scale_radar_performance_fact_20100301_0000_time" btree ("time") Check constraints: "day_scale_radar_performance_fact_20100301_0000_time_check" CHECK ("time" >=3D '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=3D# explain select distinct probe_type_num from day_scale_radar_performance_fact; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------- Unique (cost=3D1864962.35..1926416.31 rows=3D200 width=3D4) -> Sort (cost=3D1864962.35..1895689.33 rows=3D12290793 width=3D4) Sort Key: perf_reporting.day_scale_radar_performance_fact.probe_type_num -> Result (cost=3D0.00..249616.93 rows=3D12290793 width=3D4) -> Append (cost=3D0.00..249616.93 rows=3D12290793 width=3D= 4) -> Seq Scan on day_scale_radar_performance_fact (cost=3D0.00..19.90 rows=3D990 width=3D4) -> Seq Scan on day_scale_radar_performance_fact_20100401_0000 day_scale_radar_performance_fact (cost=3D0.00..31388.01 rows=3D1545501 width=3D4) -> Seq Scan on day_scale_radar_performance_fact_20100301_0000 day_scale_radar_performance_fact (cost=3D0.00..218209.02 rows=3D10744302 width=3D4) cedexis_v2=3D# explain select distinct probe_type_num from day_scale_radar_performance_fact_20100301_0000; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------- Unique (cost=3D0.00..684328.92 rows=3D3 width=3D4) -> Index Scan using day_scale_radar_performance_fact_20100301_0000_pt on day_scale_radar_performance_fact_20100301_0000 (cost=3D0.00..657468.16 rows=3D10744302 width=3D4) And this is a lot closer to what I would hope the query planner would do: cedexis_v2=3D# 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=3D781113.73..781113.84 rows=3D6 width=3D4) -> Unique (cost=3D781113.73..781113.76 rows=3D6 width=3D4) -> Sort (cost=3D781113.73..781113.75 rows=3D6 width=3D4) Sort Key: day_scale_radar_performance_fact_20100401_0000.probe_type_num -> Append (cost=3D0.00..781113.66 rows=3D6 width=3D4) -> Unique (cost=3D0.00..96784.68 rows=3D3 width=3D4) -> Index Scan using day_scale_radar_performance_fact_20100401_0000_pt on day_scale_radar_performance_fact_20100401_0000 (cost=3D0.00..92920.93 rows=3D1545501 width=3D4) -> Unique (cost=3D0.00..684328.92 rows=3D3 width=3D4= ) -> Index Scan using day_scale_radar_performance_fact_20100301_0000_pt on day_scale_radar_performance_fact_20100301_0000 (cost=3D0.00..657468.16 rows=3D10744302 width=3D4)