pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16745: delete does not prune partitions on declarative partitioned table
7+ messages / 5 participants
[nested] [flat]

* BUG #16745: delete does not prune partitions on declarative partitioned table
@ 2020-11-25 21:12 PG Bug reporting form <noreply@postgresql.org>
  2020-11-25 21:40 ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Christian <akattunga@gmail.com>
  2020-11-25 21:58 ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: PG Bug reporting form @ 2020-11-25 21:12 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: akattunga@gmail.com

The following bug has been logged on the website:

Bug reference:      16745
Logged by:          Christian Pradelli
Email address:      akattunga@gmail.com
PostgreSQL version: 13.1
Operating system:   Ubuntu
Description:        

Today I upgrade from PG-12 to PG-13

Now I detect that delete does not prune partitions on declarative
partitioned table.

table structure

CREATE TABLE public.fac_item
(
    ffacnum integer NOT NULL,
    fcoddist character varying(15) COLLATE pg_catalog."default",
    fpromoc character varying(1) COLLATE pg_catalog."default",
    fvended character varying(15) COLLATE pg_catalog."default",
    fcodpro character varying(15) COLLATE pg_catalog."default",
    fean character varying(15) COLLATE pg_catalog."default",
    fcantid double precision,
    ftotal double precision,
    ffechai date NOT NULL,
    ffechaf date,
    fartuni integer,
    fimpnum integer,
    fsucurs integer,
    fpreref numeric(18,6),
    fcliint integer,
    fdescue double precision,
    fvenpre numeric(18,6),
    CONSTRAINT pk_fac_item PRIMARY KEY (ffacnum, ffechai)
) PARTITION BY RANGE (ffechai);

If I execute:

select * FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND
FFECHAI=current_date;

it use only one partition

but:

delete FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND
FFECHAI=current_date;

scan all partitions

Is there any regression?



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16745: delete does not prune partitions on declarative partitioned table
  2020-11-25 21:12 BUG #16745: delete does not prune partitions on declarative partitioned table PG Bug reporting form <noreply@postgresql.org>
@ 2020-11-25 21:40 ` Christian <akattunga@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Christian @ 2020-11-25 21:40 UTC (permalink / raw)
  To: Christian Pradelli <akattunga@gmail.com>; pgsql-bugs@lists.postgresql.org

I investigated a little more and I found that the problem exists in PG 12
too.

I found that following command scan all partitions:

delete FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND
FFECHAI=current_date;

but if I replace current_date by a literal, it prune non
necessary partitions and scan only the right partition

delete FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND
FFECHAI='2020-11-25';

El mié, 25 nov 2020 a las 18:13, PG Bug reporting form (<
noreply@postgresql.org>) escribió:

> The following bug has been logged on the website:
>
> Bug reference:      16745
> Logged by:          Christian Pradelli
> Email address:      akattunga@gmail.com
> PostgreSQL version: 13.1
> Operating system:   Ubuntu
> Description:
>
> Today I upgrade from PG-12 to PG-13
>
> Now I detect that delete does not prune partitions on declarative
> partitioned table.
>
> table structure
>
> CREATE TABLE public.fac_item
> (
>     ffacnum integer NOT NULL,
>     fcoddist character varying(15) COLLATE pg_catalog."default",
>     fpromoc character varying(1) COLLATE pg_catalog."default",
>     fvended character varying(15) COLLATE pg_catalog."default",
>     fcodpro character varying(15) COLLATE pg_catalog."default",
>     fean character varying(15) COLLATE pg_catalog."default",
>     fcantid double precision,
>     ftotal double precision,
>     ffechai date NOT NULL,
>     ffechaf date,
>     fartuni integer,
>     fimpnum integer,
>     fsucurs integer,
>     fpreref numeric(18,6),
>     fcliint integer,
>     fdescue double precision,
>     fvenpre numeric(18,6),
>     CONSTRAINT pk_fac_item PRIMARY KEY (ffacnum, ffechai)
> ) PARTITION BY RANGE (ffechai);
>
> If I execute:
>
> select * FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND
> FFECHAI=current_date;
>
> it use only one partition
>
> but:
>
> delete FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND
> FFECHAI=current_date;
>
> scan all partitions
>
> Is there any regression?
>
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16745: delete does not prune partitions on declarative partitioned table
  2020-11-25 21:12 BUG #16745: delete does not prune partitions on declarative partitioned table PG Bug reporting form <noreply@postgresql.org>
@ 2020-11-25 21:58 ` Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-25 22:04   ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Christian <akattunga@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 2020-11-25 21:58 UTC (permalink / raw)
  To: akattunga@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> writes:
> If I execute:
> select * FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND
> FFECHAI=current_date;
> it use only one partition
> but:
> delete FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND
> FFECHAI=current_date;
> scan all partitions

> Is there any regression?

No.  There's work afoot to improve this, but it wasn't any better before.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16745: delete does not prune partitions on declarative partitioned table
  2020-11-25 21:12 BUG #16745: delete does not prune partitions on declarative partitioned table PG Bug reporting form <noreply@postgresql.org>
  2020-11-25 21:58 ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-11-25 22:04   ` Christian <akattunga@gmail.com>
  2020-11-25 22:41     ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table David G. Johnston <david.g.johnston@gmail.com>
  2020-11-26 04:49     ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table David Rowley <dgrowleyml@gmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Christian @ 2020-11-25 22:04 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org

Ok so this is a known issue.

I test the following command and works fine (it select only one partition)
but I'm don't like to much the execute command:

execute 'DELETE FROM FAC_ITEM WHERE FCODDIST='''||DSTCOD||''' AND
FSUCURS='||SUCCOD||'::integer AND
FFECHAI='''||to_char(FECHAI,'yyyy-mm-dd')||''' AND FIMPNUM<>'||IMPNUM;

Is there any other workaround?







El mié, 25 nov 2020 a las 18:58, Tom Lane (<tgl@sss.pgh.pa.us>) escribió:

> PG Bug reporting form <noreply@postgresql.org> writes:
> > If I execute:
> > select * FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND
> > FFECHAI=current_date;
> > it use only one partition
> > but:
> > delete FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND
> > FFECHAI=current_date;
> > scan all partitions
>
> > Is there any regression?
>
> No.  There's work afoot to improve this, but it wasn't any better before.
>
>                         regards, tom lane
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16745: delete does not prune partitions on declarative partitioned table
  2020-11-25 21:12 BUG #16745: delete does not prune partitions on declarative partitioned table PG Bug reporting form <noreply@postgresql.org>
  2020-11-25 21:58 ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-25 22:04   ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Christian <akattunga@gmail.com>
@ 2020-11-25 22:41     ` David G. Johnston <david.g.johnston@gmail.com>
  2020-11-25 23:46       ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Christian <akattunga@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: David G. Johnston @ 2020-11-25 22:41 UTC (permalink / raw)
  To: Christian <akattunga@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

On Wed, Nov 25, 2020 at 3:34 PM Christian <akattunga@gmail.com> wrote:

> Ok so this is a known issue.
>
> I test the following command and works fine (it select only one partition)
> but I'm don't like to much the execute command:
>
> execute 'DELETE FROM FAC_ITEM WHERE FCODDIST='''||DSTCOD||''' AND
> FSUCURS='||SUCCOD||'::integer AND
> FFECHAI='''||to_char(FECHAI,'yyyy-mm-dd')||''' AND FIMPNUM<>'||IMPNUM;
>
> Is there any other workaround?
>

Does:

EXECUTE sql USING DSTCOD, SUCCOD, etc...
sql = ... where FCODDIST=$1 AND FSUCURS=$2, etc...
work?

or

format(sql, DSTCOD, etc...)
sql = ... where FCODDIST=%L AND FSUCURS=%L, etc...

David J.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16745: delete does not prune partitions on declarative partitioned table
  2020-11-25 21:12 BUG #16745: delete does not prune partitions on declarative partitioned table PG Bug reporting form <noreply@postgresql.org>
  2020-11-25 21:58 ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-25 22:04   ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Christian <akattunga@gmail.com>
  2020-11-25 22:41     ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table David G. Johnston <david.g.johnston@gmail.com>
@ 2020-11-25 23:46       ` Christian <akattunga@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Christian @ 2020-11-25 23:46 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

Yes, it works fine in both cases, it only use the right partition.

El mié, 25 nov 2020 a las 19:42, David G. Johnston (<
david.g.johnston@gmail.com>) escribió:

> On Wed, Nov 25, 2020 at 3:34 PM Christian <akattunga@gmail.com> wrote:
>
>> Ok so this is a known issue.
>>
>> I test the following command and works fine (it select only one
>> partition) but I'm don't like to much the execute command:
>>
>> execute 'DELETE FROM FAC_ITEM WHERE FCODDIST='''||DSTCOD||''' AND
>> FSUCURS='||SUCCOD||'::integer AND
>> FFECHAI='''||to_char(FECHAI,'yyyy-mm-dd')||''' AND FIMPNUM<>'||IMPNUM;
>>
>> Is there any other workaround?
>>
>
> Does:
>
> EXECUTE sql USING DSTCOD, SUCCOD, etc...
> sql = ... where FCODDIST=$1 AND FSUCURS=$2, etc...
> work?
>
> or
>
> format(sql, DSTCOD, etc...)
> sql = ... where FCODDIST=%L AND FSUCURS=%L, etc...
>
> David J.
>
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16745: delete does not prune partitions on declarative partitioned table
  2020-11-25 21:12 BUG #16745: delete does not prune partitions on declarative partitioned table PG Bug reporting form <noreply@postgresql.org>
  2020-11-25 21:58 ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-25 22:04   ` Re: BUG #16745: delete does not prune partitions on declarative partitioned table Christian <akattunga@gmail.com>
@ 2020-11-26 04:49     ` David Rowley <dgrowleyml@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: David Rowley @ 2020-11-26 04:49 UTC (permalink / raw)
  To: Christian <akattunga@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

On Thu, 26 Nov 2020 at 11:34, Christian <akattunga@gmail.com> wrote:
> Ok so this is a known issue.

The reason you don't get the behaviour that you'd like is that there
is no run-time partition pruning for UPDATE/DELETE.  The current_date
cannot be evaluated during query planning, so plan-time partition
pruning cannot be done then.

If you know for certain that you or your client never prepare queries,
meaning, planning always takes place just before execution, then you
might be able to get away with:

delete FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND FFECHAI =
'today'::date;

The 'today'::date will be evaluated earlier enough that the planner
will be able to perform partition pruning using the current date.

Just be aware, if you do cache plans somewhere the date will be cached
along with them. Things will start going badly for you after midnight.

David





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2020-11-26 04:49 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-25 21:12 BUG #16745: delete does not prune partitions on declarative partitioned table PG Bug reporting form <noreply@postgresql.org>
2020-11-25 21:40 ` Christian <akattunga@gmail.com>
2020-11-25 21:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-25 22:04   ` Christian <akattunga@gmail.com>
2020-11-25 22:41     ` David G. Johnston <david.g.johnston@gmail.com>
2020-11-25 23:46       ` Christian <akattunga@gmail.com>
2020-11-26 04:49     ` David Rowley <dgrowleyml@gmail.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