BUG #16745: delete does not prune partitions on declarative partitioned table
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?
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ó:
Show quoted text
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?
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
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ó:
Show quoted text
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 partitionsIs there any regression?
No. There's work afoot to improve this, but it wasn't any better before.
regards, tom lane
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.
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ó:
Show quoted text
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.
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