Postgresql partitions: Abnormally high seq scan cost on master table

626 Views Asked by At

I have a little database of a few hundreds of millions of rows for storing call detail records. I setup partitioning as per:

http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

and it seemed to work pretty well until now. I have master table "acmecdr" which has rules for inserting into the correct partition and check constraints to make sure the correct table is used when selecting data. Here is an example of one of the partitions:

cdrs=> \d acmecdr_20130811
                                Table "public.acmecdr_20130811"
            Column             |  Type   |                      Modifiers
-------------------------------+---------+------------------------------------------------------
 acmecdr_id                    | bigint  | not null default 
...snip...
 h323setuptime                 | bigint  |
 acmesipstatus                 | integer |
 acctuniquesessionid           | text    |
 customers_id                  | integer |
Indexes:
    "acmecdr_20130811_acmesessionegressrealm_idx" btree (acmesessionegressrealm)
    "acmecdr_20130811_acmesessioningressrealm_idx" btree (acmesessioningressrealm)
    "acmecdr_20130811_calledstationid_idx" btree (calledstationid)
    "acmecdr_20130811_callingstationid_idx" btree (callingstationid)
    "acmecdr_20130811_h323setuptime_idx" btree (h323setuptime)
Check constraints:
    "acmecdr_20130811_h323setuptime_check" CHECK (h323setuptime >= 1376179200 AND h323setuptime < 1376265600)
Inherits: acmecdr

Now as one would expect with SET constraint_exclusion = on the correct partition should automatically be preferred and since there is an index on it there should only be one index scan.

However:

cdrs=> explain analyze select * from acmecdr where h323setuptime > 1376179210 and h323setuptime < 1376179400;
                                                                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..1435884.93 rows=94 width=1130) (actual time=138857.660..138858.778 rows=112 loops=1)
   ->  Append  (cost=0.00..1435884.93 rows=94 width=1130) (actual time=138857.628..138858.189 rows=112 loops=1)
         ->  Seq Scan on acmecdr  (cost=0.00..1435863.60 rows=1 width=1137) (actual time=138857.584..138857.584 rows=0 loops=1)
              Filter: ((h323setuptime > 1376179210) AND (h323setuptime < 1376179400))
         ->  Index Scan using acmecdr_20130811_h323setuptime_idx on acmecdr_20130811 acmecdr  (cost=0.00..21.33 rows=93 width=1130) (actual time=0.037..0.283 rows=112 loops=1)
               Index Cond: ((h323setuptime > 1376179210) AND (h323setuptime < 1376179400))
 Total runtime: 138859.240 ms
(7 rows)

So, I can see it's not scanning all the partitions, only the relevant one (which in index scan and pretty quick) and also the master table (which seems to be normal from the examples I've seen). But the high cost of the seq scan on the master table seems to be abnormal. I would love for that to come down and I see no reason for it, especially since the master table does not have any records in it:

cdrs=> select count(*) from only acmecdr;
 count
-------
     0
(1 row)

Unless I'm missing something obvious, this query should be quick. But it's not - it takes about 2 minutes? This does not seem normal at all (even for a slow server).

I'm out of ideas of what to try next, so if anyone has any suggestions or pointers in the right direction, it would be very much appreciated.

0

There are 0 best solutions below