On Wed, Jan 27, 2016 at 5:09 PM, rverghese <ri...@hotmail.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)