Re: force partition pruning

Fair enough. Many thanks for taking time out to follow up and clear my misunderstanding. I’ll not pollute the thread , since OP got what he wanted. But I’ll have to spend more time trying to simulate it with data and reread what you want to say :). But thanks again for clearing that up. On Wed,

SV: force partition pruning

Fra: David Rowley Sendt: 12. maj 2021 02:34 >> >> ok i think i just may be there is very less data , hence no index scan, no >> pruning. >> >> when i try to force seq_scan off, >> > >Unfortunately, no run-time pruning occurred in the above plan. > >The fact that the above plan uses Append made

Re: force partition pruning

On Wed, 12 May 2021 at 06:33, Vijaykumar Jain wrote: > > ok i think i just may be there is very less data , hence no index scan, no > pruning. > > when i try to force seq_scan off, > > postgres=# set enable_seqscan TO off; > SET > postgres=# explain analyze select * from tprt where tprt.col1 in (

Re: force partition pruning

ok i think i just may be there is very less data , hence no index scan, no pruning. when i try to force seq_scan off, postgres=# set enable_seqscan TO off; SET postgres=# explain analyze select * from tprt where tprt.col1 in (select tbl1.col1 from tbl1 where tbl1.col2 in (1, 2) );

Re: force partition pruning

ok, partitioning - Can PostgreSQL 12 do partition pruning at execution time with subquery returning a list? - Stack Overflow ok forcing hash join off, did not work as th

Re: force partition pruning

Ok. maybe you are in a rush. But I would keep the thread open, to understand what I am not understanding or else, it'll become a habit of converting sql to plpgsql :) Big Guys, It seems, when the table is partitioned by range, it makes use of a nested loop which helps in partition pruning. if the

SV: force partition pruning

> >Sorry, > >I made a major mistake. I somehow saw the period and period_version as the >same. >so, yes partitions are not pruned here. So my suggestion makes no sense. Thats quite ok. I think my plan now is to have a table returning function that executes a query dynamically. The query has a wh

Re: force partition pruning

Sorry, I made a major mistake. I somehow saw the period and period_version as the same. so, yes partitions are not pruned here. So my suggestion makes no sense. *** create table register(period_version text) partition by list ( period_version ); create table register_p1 part

Re: force partition pruning

I do not know how to put this in words, but see below when the predicate is explicitly applied to the main table with partition. postgres=# \d+ prt1 Partitioned table "public.prt1" Column | Type| Collation | Nullable | Default | Storage | Stats ta

force partition pruning

Hi all I need a litte advice on how to Postgres 13.2 A metadata query pulls partition keys: select m.period_version from register.register_metadata m where m.current_version and m.period between '201712' and '201912' ; A query using these in an in-list easily makes the planner do partition pr