Re: [PERFORM] Postgres partitions-query scanning all child tables

[rverghese]

Yeah that would be a pain to have the date_part in each query. Thanks for the 
info!

Date: Thu, 28 Jan 2016 00:48:10 -0700
From: ml-node+s1045698n5884581...@n5.nabble.com
To: ri...@hotmail.com
Subject: Re: Postgres partitions-query scanning all child tables



On Thu, Jan 28, 2016 at 1:10 AM, rverghese <[hidden email]> wrote:



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


If you wanted to have a column called month_num or something like that, and if 
*all* of your queries extract the month date_part() in every where clause, then 
yes, you could have just 12 tables.

But you won't like that partitioning scheme for other reasons:- queries that 
don't "play by the rules" will be slow- very old data will slow down recent-day 
queries- no ability to quickly remove obsolete data by dropping partitions that 
are no longer useful













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-tp5884497p5884581.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-tp5884497p5884729.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Re: [PERFORM] Postgres partitions-query scanning all child tables

[Corey Huinker]

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

Re: [PERFORM] Postgres partitions-query scanning all child tables

[rverghese]

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+s1045698n5884560...@n5.nabble.com
To: ri...@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 

Re: [PERFORM] Postgres partitions-query scanning all child tables

[Corey Huinker]

On Thu, Jan 28, 2016 at 1:10 AM, rverghese  wrote:

> 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
>
>
If you wanted to have a column called month_num or something like that, and
if *all* of your queries extract the month date_part() in every where
clause, then yes, you could have just 12 tables.

But you won't like that partitioning scheme for other reasons:
- queries that don't "play by the rules" will be slow
- very old data will slow down recent-day queries
- no ability to quickly remove obsolete data by dropping partitions that
are no longer useful