Re: Postgres partitions-query scanning all child tables

On Wed, Jan 27, 2016 at 5:09 PM, rverghese <riyav(at)hotmail(dot)com> wrote:

> Hi I have a master table and the inherited tables broken up by month.
>
> /e.g. CONSTRAINT transactions_january_log_date_check CHECK
> (date_part('month'::text, log_date) = 1::double precision);/
>
> So transactions_master is the master table, and then transactions_january,
> transactions_february, etc. I have the rules in place and an index on the
> date field in each child table. Currently i only have data in the january
> table. But when I query the master table.
>
> /explain select * from transactions_master where log_tstamp='1/23/2016'
> /
>
> I see that it goes through all the tables. Should it be querying the
> january
> table first? And not do the others once its comes across the data in
> january?
>
> 'Append (cost=0.00..82.88 rows=37 width=165)'
> ' -> Seq Scan on transactions_master (cost=0.00..0.00 rows=1 width=176)'
> ' Filter: (log_logdate = '2016-01-23 00:00:00'::timestamp without
> time zone)'
> ' -> Bitmap Heap Scan on transactions_february (cost=2.16..5.29 rows=2
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_feb_logdate (cost=0.00..2.16
> rows=2 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Heap Scan on transactions_january (cost=2.16..5.29 rows=2
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_jan_logdate (cost=0.00..2.16
> rows=2 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Heap Scan on transactions_march (cost=2.16..5.29 rows=2
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_mar_system (cost=0.00..2.16
> rows=2 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Heap Scan on transactions_april (cost=2.16..5.29 rows=2
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_apr_logdate (cost=0.00..2.16
> rows=2 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Heap Scan on transactions_may (cost=2.16..5.29 rows=2
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_may_logdate (cost=0.00..2.16
> rows=2 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Heap Scan on transactions_june (cost=2.16..5.34 rows=2
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_jun_logdate (cost=0.00..2.16
> rows=2 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Heap Scan on transactions_july (cost=2.31..8.82 rows=4
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_jul_logdate (cost=0.00..2.30
> rows=4 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Index Scan using idx_trans_aug_logdate on transactions_august
> (cost=0.29..9.97 rows=5 width=96)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without
> time zone)'
> ' -> Bitmap Heap Scan on transactions_september (cost=2.31..8.79 rows=4
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_sep_logdate (cost=0.00..2.30
> rows=4 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Heap Scan on transactions_november (cost=2.31..8.14 rows=4
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_nov_logdate (cost=0.00..2.30
> rows=4 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Heap Scan on transactions_december (cost=2.30..7.14 rows=3
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_dec_logdate (cost=0.00..2.30
> rows=3 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Heap Scan on transactions_october (cost=2.31..8.22 rows=4
> width=176)'
> ' Recheck Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
> ' -> Bitmap Index Scan on idx_trans_oct_logdate (cost=0.00..2.30
> rows=4 width=0)'
> ' Index Cond: (log_logdate = '2016-01-23 00:00:00'::timestamp
> without time zone)'
>

http://www.postgresql.org/message-id/1121251997.3970.237.camel@localhost.localdomain

tl;dr - constraint exclusion only works with IN, BETWEEN, =, <, <=, >, >=,
<> and only where values are immutable.

I ran into this when attempting to use <@ operators for my range
partitioning extension.

So date_part() won't work because constraint exclusion can't see into it.

You'll have better luck with something like
CHECK(log_date >= '2016-01-01'::timestamp and log_date <
'2016-02-01'::timestamp)