performance - Efficient querying of partition master PostgreSQL table -
i'm talking 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 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 each row execute procedure history_log_insert_trigger() and set of child tables partitioned 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 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 time zone , tstamp < '2013-05-01 00:00:00+04'::timestamp time zone) inherits: history_log so problem - when queries have condition constrained tstamp directly on child tables - works fast.
logstore=# explain select userid history_log_201304 tstamp >= (current_date - interval '3 days')::date::timestamptz , tstamp < current_date::timestamptz , 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) , (tstamp < ('now'::cstring)::date)) filter: (action = 13) but when try same on master table - goes seq scan:
logstore=# explain select userid history_log tstamp >= (current_date - interval '3 days')::date::timestamptz , tstamp < current_date::timestamptz , 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) , (tstamp < ('now'::cstring)::date) , (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) , (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) , (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) , (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) , (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) , (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) , (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) , (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) , (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) , (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) , (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) , (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) , (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) , (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) , (tstamp < ('now'::cstring)::date)) filter: (action = 13) what's happening here? why queries master table isn't fast?
i have constraint_exclusion set on.
edit: found solution accident , writing here sake of readability.
till today had wrong constraints - tstamp column of timestamp time zone type, constraints build on timestamp without time zone. fixed that, fixed queries have type casts - still queries master table took minutes instead of seconds. last option went so. during conversation went db , issued explain analyze child tables actual numbers - , after queries on master table became fast!
the query should fast. seq scan being performed on master table only, which, given properly-configured partitioned table, should contain no rows @ all.
consider using explain analyze can see how long query taking. difference between 2 should negligible.
the actual problem appears query executed on child tables not return results. presumably question boils down to: why child tables check constraint cannot possibly met still being searched?
there a thread on pgsql-bugs mailing list issue. tstamp column timestamp time zone. check cannot used expression in where clause date value, not timestamp. consider using current_timestamp instead of current_date. if need query midnight, keep current query, add cast exact same type tstamp column has (::timestamp time zone).
Comments
Post a Comment