Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aOfoi-0000ZU-3m for pgsql-performance@arkaria.postgresql.org; Thu, 28 Jan 2016 06:12:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aOfoh-0007J0-Lo for pgsql-performance@arkaria.postgresql.org; Thu, 28 Jan 2016 06:12:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aOfn2-0005Tf-NE for pgsql-performance@postgresql.org; Thu, 28 Jan 2016 06:10:44 +0000 Received: from mbob.nabble.com ([162.253.133.15]) by magus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1aOfmv-0006mx-Mn for pgsql-performance@postgresql.org; Thu, 28 Jan 2016 06:10:42 +0000 Received: from msam.nabble.com (unknown [162.253.133.85]) by mbob.nabble.com (Postfix) with ESMTP id 797431F5BB3F for ; Wed, 27 Jan 2016 22:06:08 -0800 (PST) Date: Wed, 27 Jan 2016 23:10:35 -0700 (MST) From: rverghese To: pgsql-performance@postgresql.org Message-ID: In-Reply-To: References: <1453932590151-5884497.post@n5.nabble.com> Subject: Re: Postgres partitions-query scanning all child tables MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_683439_354476680.1453961435308" X-Pg-Spam-Score: -1.2 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ------=_Part_683439_354476680.1453961435308 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Ok, thanks. Thats a bummer though. That means I need a table for every month/year combination. I was hoping to limit it to 12 tables. Riya Date: Wed, 27 Jan 2016 21:31:35 -0700 From: ml-node+s1045698n5884560h11@n5.nabble.com To: riyav@hotmail.com Subject: Re: Postgres partitions-query scanning all child tables On Wed, Jan 27, 2016 at 5:09 PM, rverghese <[hidden email]> 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@... 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) If you reply to this email, your message will be added to the discussion below: http://postgresql.nabble.com/Postgres-partitions-query-scanning-all-child-tables-tp5884497p5884560.html To unsubscribe from Postgres partitions-query scanning all child tables, click here. NAML -- View this message in context: http://postgresql.nabble.com/Postgres-partitions-query-scanning-all-child-tables-tp5884497p5884570.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. ------=_Part_683439_354476680.1453961435308 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
Ok, thanks. Thats a bummer though. That means I need a table for every month/year combination. I was hoping to limit it to 12 tables.

Riya

Date: Wed, 27 Jan 2016 21:31:35 -0700
From: [hidden email]
To: [hidden email]
Subject: Re: Postgres partitions-query scanning all child tables

On Wed, Jan 27, 2016 at 5:09 PM, rverghese <[hidden email]> 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)'



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)



If you reply to this email, your message will be added to the discussion below:
http://postgresql.nabble.com/Postgres-partitions-query-scanning-all-child-tables-tp5884497p5884560.html
To unsubscribe from Postgres partitions-query scanning all child tables, click here.
NAML

View this message in context: RE: Postgres partitions-query scanning all child tables
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
------=_Part_683439_354476680.1453961435308--