pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpartition pruning
6+ messages / 6 participants
[nested] [flat]
* partition pruning
@ 2010-03-01 19:29 Anj Adu <fotographs@gmail.com>
2010-03-04 22:40 ` Re: partition pruning Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Anj Adu @ 2010-03-01 19:29 UTC (permalink / raw)
To: pgsql-performance
When I use intervals in my query e.g col1 between current_timestamp -
interval '10 days' and current_timestamp...the optimizer checks ALL
partitions whereas if I use col1 between 2 hardcoded dates..only
the applicable partitions are scanned.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: partition pruning
2010-03-01 19:29 partition pruning Anj Adu <fotographs@gmail.com>
@ 2010-03-04 22:40 ` Robert Haas <robertmhaas@gmail.com>
2010-03-08 22:39 ` Re: partition pruning Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 1 reply; 6+ messages in thread
From: Robert Haas @ 2010-03-04 22:40 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; +Cc: pgsql-performance
On Mon, Mar 1, 2010 at 2:29 PM, Anj Adu <fotographs@gmail.com> wrote:
> When I use intervals in my query e.g col1 between current_timestamp -
> interval '10 days' and current_timestamp...the optimizer checks ALL
> partitions whereas if I use col1 between 2 hardcoded dates..only
> the applicable partitions are scanned.
Yep. This is one example of a more general principle:
constant-folding happens before planning, but anything more complex
has to wait until execution time. So the plan can't take into account
the value of current_timestamp in forming the plan.
Unfortunately I don't think there's really any easy way around this:
you have to do select current_timestamp, current_timestamp - interval
'10 days' first and then build & execute a new query.
...Robert
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: partition pruning
2010-03-01 19:29 partition pruning Anj Adu <fotographs@gmail.com>
2010-03-04 22:40 ` Re: partition pruning Robert Haas <robertmhaas@gmail.com>
@ 2010-03-08 22:39 ` Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 0 replies; 6+ messages in thread
From: Simon Riggs @ 2010-03-08 22:39 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Anj Adu <fotographs@gmail.com>; pgsql-performance
On Thu, 2010-03-04 at 17:40 -0500, Robert Haas wrote:
> On Mon, Mar 1, 2010 at 2:29 PM, Anj Adu <fotographs@gmail.com> wrote:
> > When I use intervals in my query e.g col1 between current_timestamp -
> > interval '10 days' and current_timestamp...the optimizer checks ALL
> > partitions whereas if I use col1 between 2 hardcoded dates..only
> > the applicable partitions are scanned.
>
> Yep. This is one example of a more general principle:
> constant-folding happens before planning, but anything more complex
> has to wait until execution time. So the plan can't take into account
> the value of current_timestamp in forming the plan.
It could, but it doesn't yet. Partition removal can take place in the
executor and this is currently targeted for 9.1.
--
Simon Riggs www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: partition pruning
@ 2019-02-14 12:37 Laurenz Albe <laurenz.albe@cybertec.at>
2019-02-14 12:41 ` Re: partition pruning suganthi Sekar <suganthi@uniphore.com>
2019-02-14 14:36 ` Re: partition pruning Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 6+ messages in thread
From: Laurenz Albe @ 2019-02-14 12:37 UTC (permalink / raw)
To: suganthi Sekar <suganthi@uniphore.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
suganthi Sekar wrote:
> i am using Postgresql 11, i have 2 partition table , when i joined both table in query
> a table its goes exact partition table , but other table scan all partition
>
> please clarify on this .
>
> i have enabled below parameter on in configuration file
> Note : alter system set enable_partitionwise_join to 'on';
>
>
> Example :
>
> explain analyze
> select * from call_report1 as a inner join call_report2 as b on a.call_id=b.call_id
> where a.call_created_date ='2017-11-01' and '2017-11-30'
>
>
>
> "Hash Right Join (cost=8.19..50.47 rows=2 width=3635) (actual time=0.426..0.447 rows=7 loops=1)"
> " Hash Cond: (b.call_id = a.call_id)"
> " -> Append (cost=0.00..41.81 rows=121 width=2319) (actual time=0.040..0.170 rows=104 loops=1)"
> " -> Seq Scan on call_report2 b (cost=0.00..0.00 rows=1 width=528) (actual time=0.010..0.010 rows=0 loops=1)"
> " -> Seq Scan on call_report2_201803 b_1 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.029..0.031 rows=14 loops=1)"
> " -> Seq Scan on call_report2_201711 b_2 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.014..0.015 rows=7 loops=1)"
> " -> Seq Scan on call_report2_201712 b_3 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.047 rows=34 loops=1)"
> " -> Seq Scan on call_report2_201801 b_4 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.058 rows=49 loops=1)"
> " -> Hash (cost=8.17..8.17 rows=2 width=1314) (actual time=0.104..0.104 rows=7 loops=1)"
> " Buckets: 1024 Batches: 1 Memory Usage: 12kB"
> " -> Append (cost=0.00..8.17 rows=2 width=1314) (actual time=0.053..0.060 rows=7 loops=1)"
> " -> Seq Scan on call_report1 a (cost=0.00..0.00 rows=1 width=437) (actual time=0.022..0.022 rows=0 loops=1)"
> " Filter: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))"
> " -> Index Scan using idx_call_report1_201711_ccd on call_report1_201711 a_1 (cost=0.14..8.16 rows=1 width=2190) (actual time=0.029..0.034 rows=7 loops=1)"
> " Index Cond: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))"
> "Planning Time: 20.866 ms"
> "Execution Time: 1.205 ms"
There is no condition on the table "call_report2" in your query,
so it is not surprising that all partitions are scanned, right?
You have to add a WHERE condition that filters on the partitioning
column(s) of "call_report2".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: partition pruning
2019-02-14 12:37 Re: partition pruning Laurenz Albe <laurenz.albe@cybertec.at>
@ 2019-02-14 12:41 ` suganthi Sekar <suganthi@uniphore.com>
1 sibling, 0 replies; 6+ messages in thread
From: suganthi Sekar @ 2019-02-14 12:41 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
HI ,
Ok thanks.
Regards,
Suganthi Sekar
________________________________
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: 14 February 2019 18:07:49
To: suganthi Sekar; pgsql-performance@lists.postgresql.org
Subject: Re: partition pruning
suganthi Sekar wrote:
> i am using Postgresql 11, i have 2 partition table , when i joined both table in query
> a table its goes exact partition table , but other table scan all partition
>
> please clarify on this .
>
> i have enabled below parameter on in configuration file
> Note : alter system set enable_partitionwise_join to 'on';
>
>
> Example :
>
> explain analyze
> select * from call_report1 as a inner join call_report2 as b on a.call_id=b.call_id
> where a.call_created_date ='2017-11-01' and '2017-11-30'
>
>
>
> "Hash Right Join (cost=8.19..50.47 rows=2 width=3635) (actual time=0.426..0.447 rows=7 loops=1)"
> " Hash Cond: (b.call_id = a.call_id)"
> " -> Append (cost=0.00..41.81 rows=121 width=2319) (actual time=0.040..0.170 rows=104 loops=1)"
> " -> Seq Scan on call_report2 b (cost=0.00..0.00 rows=1 width=528) (actual time=0.010..0.010 rows=0 loops=1)"
> " -> Seq Scan on call_report2_201803 b_1 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.029..0.031 rows=14 loops=1)"
> " -> Seq Scan on call_report2_201711 b_2 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.014..0.015 rows=7 loops=1)"
> " -> Seq Scan on call_report2_201712 b_3 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.047 rows=34 loops=1)"
> " -> Seq Scan on call_report2_201801 b_4 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.058 rows=49 loops=1)"
> " -> Hash (cost=8.17..8.17 rows=2 width=1314) (actual time=0.104..0.104 rows=7 loops=1)"
> " Buckets: 1024 Batches: 1 Memory Usage: 12kB"
> " -> Append (cost=0.00..8.17 rows=2 width=1314) (actual time=0.053..0.060 rows=7 loops=1)"
> " -> Seq Scan on call_report1 a (cost=0.00..0.00 rows=1 width=437) (actual time=0.022..0.022 rows=0 loops=1)"
> " Filter: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))"
> " -> Index Scan using idx_call_report1_201711_ccd on call_report1_201711 a_1 (cost=0.14..8.16 rows=1 width=2190) (actual time=0.029..0.034 rows=7 loops=1)"
> " Index Cond: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))"
> "Planning Time: 20.866 ms"
> "Execution Time: 1.205 ms"
There is no condition on the table "call_report2" in your query,
so it is not surprising that all partitions are scanned, right?
You have to add a WHERE condition that filters on the partitioning
column(s) of "call_report2".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: partition pruning
2019-02-14 12:37 Re: partition pruning Laurenz Albe <laurenz.albe@cybertec.at>
@ 2019-02-14 14:36 ` Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 6+ messages in thread
From: Justin Pryzby @ 2019-02-14 14:36 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: suganthi Sekar <suganthi@uniphore.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Thu, Feb 14, 2019 at 01:37:49PM +0100, Laurenz Albe wrote:
> There is no condition on the table "call_report2" in your query,
> so it is not surprising that all partitions are scanned, right?
Some people find it surprising, since: a.call_id=b.call_id
suganthi Sekar wrote:
> > explain analyze
> > select * from call_report1 as a inner join call_report2 as b on a.call_id=b.call_id
> > where a.call_created_date ='2017-11-01' and '2017-11-30'
Justin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-02-14 14:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-01 19:29 partition pruning Anj Adu <fotographs@gmail.com>
2010-03-04 22:40 ` Robert Haas <robertmhaas@gmail.com>
2010-03-08 22:39 ` Simon Riggs <simon@2ndQuadrant.com>
2019-02-14 12:37 Re: partition pruning Laurenz Albe <laurenz.albe@cybertec.at>
2019-02-14 12:41 ` suganthi Sekar <suganthi@uniphore.com>
2019-02-14 14:36 ` Justin Pryzby <pryzby@telsasoft.com>
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