From: rverghese <riyav@hotmail.com>
To: pgsql-performance@postgresql.org
Subject: Postgres partitions-query scanning all child tables
Date: Wed, 27 Jan 2016 15:09:50 -0700 (MST)
Message-ID: <1453932590151-5884497.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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)'
--
View this message in context: http://postgresql.nabble.com/Postgres-partitions-query-scanning-all-child-tables-tp5884497.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: riyav@hotmail.com
Subject: Re: Postgres partitions-query scanning all child tables
In-Reply-To: <1453932590151-5884497.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox