Thread: BUG #16745: delete does not prune partitions on declarative partitioned table

BUG #16745: delete does not prune partitions on declarative partitioned table

From

PG Bug reporting form

Date:

26 November 2020, 00:12:49

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?

Re: BUG #16745: delete does not prune partitions on declarative partitioned table

From

Christian

Date:

26 November 2020, 00:40:11

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?

Re: BUG #16745: delete does not prune partitions on declarative partitioned table

From

Tom Lane

Date:

26 November 2020, 00:58:01

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

Re: BUG #16745: delete does not prune partitions on declarative partitioned table

From

Christian

Date:

26 November 2020, 01:04:41

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

Re: BUG #16745: delete does not prune partitions on declarative partitioned table

From

"David G. Johnston"

Date:

26 November 2020, 01:41:50

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?

format(sql, DSTCOD, etc...)

sql = ... where FCODDIST=%L AND FSUCURS=%L, etc...

David J.

Re: BUG #16745: delete does not prune partitions on declarative partitioned table

From

Christian

Date:

26 November 2020, 02:46:14

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.

Re: BUG #16745: delete does not prune partitions on declarative partitioned table

From

David Rowley

Date:

26 November 2020, 07:49:05

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