0

I'm talking about this feature.

I have master table:

logstore=# \d history_log
                                   Table "public.history_log"
  Column   |           Type           |                       
-----------+--------------------------+-----------------------------------------------------------
 id        | bigint                   | NOT NULL DEFAULT nextval('history_log__id_seq'::regclass)
 tstamp    | timestamp with time zone | NOT NULL DEFAULT now()
 session   | character varying(40)    |
 action    | smallint                 | NOT NULL
 userid    | integer                  |
 urlid     | integer                  |
Indices:
    "history_log__id_pkey" PRIMARY KEY, btree (id)
Triggers:
    insert_history_log_trigger BEFORE INSERT ON history_log FOR EACH ROW EXECUTE PROCEDURE history_log_insert_trigger()

and a set of child tables partitioned by tstamp column:

logstore=# \d history_log_201304
                               Table "public.history_log_201304"
  Column   |           Type           |                       
-----------+--------------------------+-----------------------------------------------------------
 id        | bigint                   | NOT NULL DEFAULT nextval('history_log__id_seq'::regclass)
 tstamp    | timestamp with time zone | NOT NULL DEFAULT now()
 session   | character varying(40)    |
 action    | smallint                 | NOT NULL
 userid    | integer                  |
 urlid     | integer                  |
Indices:
    "history_log_201304_pkey" PRIMARY KEY, btree (id)
    "history_log_201304_tstamp" btree (tstamp)
    "history_log_201304_userid" btree (userid)
Constraints:
    "history_log_201304_tstamp_check" CHECK (tstamp >= '2013-04-01 00:00:00+04'::timestamp with time zone AND tstamp < '2013-05-01 00:00:00+04'::timestamp with time zone)
Inherits: history_log

So what is my problem - when I do queries that have WHERE condition constrained by tstamp directly on child tables - it works very fast.

logstore=# EXPLAIN SELECT userid FROM history_log_201304 WHERE tstamp >= (current_date - interval '3 days')::date::timestamptz AND tstamp < current_date::timestamptz AND action = 13;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using history_log_201304_tstamp on history_log_201304  (cost=0.01..8.37 rows=1 width=4)
   Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
   Filter: (action = 13)

But when I try to do the same on master table - it goes to Seq Scan:

logstore=# EXPLAIN SELECT userid FROM history_log WHERE tstamp >= (current_date - interval '3 days')::date::timestamptz AND tstamp < current_date::timestamptz AND action = 13;
                                                                    QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
---------------
 Result  (cost=0.00..253099.82 rows=1353838 width=4)
   ->  Append  (cost=0.00..253099.82 rows=1353838 width=4)
         ->  Seq Scan on history_log  (cost=0.00..0.00 rows=1 width=4)
               Filter: ((action = 13) AND (tstamp < ('now'::cstring)::date) AND (tstamp >= ((('now'::cstring)::date - '3 days'::inte
rval))::date))
         ->  Index Scan using history_log_201203_tstamp on history_log_201203 history_log  (cost=0.01..9.67 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201204_tstamp on history_log_201204 history_log  (cost=0.01..9.85 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201205_tstamp on history_log_201205 history_log  (cost=0.01..10.39 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201206_tstamp on history_log_201206 history_log  (cost=0.01..10.32 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201207_tstamp on history_log_201207 history_log  (cost=0.01..10.09 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201208_tstamp on history_log_201208 history_log  (cost=0.01..10.35 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201209_tstamp on history_log_201209 history_log  (cost=0.01..10.53 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201210_tstamp on history_log_201210 history_log  (cost=0.01..11.83 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201211_tstamp on history_log_201211 history_log  (cost=0.01..11.87 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201212_tstamp on history_log_201212 history_log  (cost=0.01..12.40 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201301_tstamp on history_log_201301 history_log  (cost=0.01..12.35 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201302_tstamp on history_log_201302 history_log  (cost=0.01..12.35 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201303_tstamp on history_log_201303 history_log  (cost=0.01..252959.45 rows=1353824 width=
4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)
         ->  Index Scan using history_log_201304_tstamp on history_log_201304 history_log  (cost=0.01..8.37 rows=1 width=4)
               Index Cond: ((tstamp >= ((('now'::cstring)::date - '3 days'::interval))::date) AND (tstamp < ('now'::cstring)::date))
               Filter: (action = 13)

What's happening here? Why queries to master table isn't just as fast?

I have constraint_exclusion set to on.

Edit: I found solution by accident and writing it here for the sake of readability.

Till today I had wrong constraints - my tstamp column is of timestamp WITH time zone type, constraints were build on timestamp WITHOUT time zone. I fixed that, fixed my queries to have type casts - but still queries to master table took minutes instead of seconds. That was my last option so I went to SO. During conversation I went to DB and issued EXPLAIN ANALYZE to all child tables to get some actual numbers - and after that queries on master table became fast!

Improve this question

1 Answer 1

Reset to default
1

The query should be just as fast. The seq scan is being performed on the master table only, which, given a properly-configured partitioned table, should contain no rows at all.

Consider using EXPLAIN ANALYZE so you can see exactly how long the query is taking. The difference between the two should be negligible.

The actual problem appears to be that the query is executed on child tables that will not return any results. Presumably your question boils down to: why are the child tables where the CHECK constraint cannot possibly be met still being searched?

There is a thread on the pgsql-bugs mailing list about this very issue. Your tstamp column is a timestamp with time zone. The check cannot be used as the expression in your WHERE clause is a date value, not a timestamp. Consider using CURRENT_TIMESTAMP instead of CURRENT_DATE. If you need to query from midnight, then keep your current query, but add a cast to the exact same type that the tstamp column has (::timestamp with time zone).

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

It takes AGES. How could I check if my master table for some reason still contains rows? I believe all queries to it are proxied to child tables...
@skaurus The queries are indeed proxied, as though you had executed the query on each table (including the master) and placed UNION ALL between them. What's probably taking time is not the query on the actual master table, but the index scan on the other child tables.
Errm... Just to give some actual numbers I went to DB and issued EXPLAIN ANALYZE to all childs tables (it took about 2.5 sec in sum); then I issued same command to master table - and it took 2 seconds too! I'm embarassed. That wasn't true even few hours ago. Little background - till today I had constraints on each table with timestamp WITHOUT time zone and I've just fixed it - but didn't seen any changes (EXPLAIN ANALYZE on master table nearly killed my app). That's why I decided to ask for help. And now - all is good. It seems that ANALYZE of all child tables was needed :-) Thank you!
@skaurus No problem. It's the common "after asking for help I found the problem" occurrence. :)
Indeed :) Considering new part of your answer - I believe I have type casts in provided examples.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.