Re: [PATCH] Erase the distinctClause if the result is unique by definition - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Re: [PATCH] Erase the distinctClause if the result is unique by definition
Date
Msg-id CAExHW5s88nwu3Loxb06Z61Dq-hU1wGw_pQjRrbhu1kNGmr-p7A@mail.gmail.com
Whole thread Raw
In response to Re: [PATCH] Erase the distinctClause if the result is unique by definition  (Andy Fan <zhihui.fan1213@gmail.com>)
Responses Re: [PATCH] Erase the distinctClause if the result is unique bydefinition
Re: [PATCH] Erase the distinctClause if the result is unique by definition
List pgsql-hackers


On Tue, Feb 11, 2020 at 8:27 AM Andy Fan <zhihui.fan1213@gmail.com> wrote:


On Tue, Feb 11, 2020 at 12:22 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:



[PATCH] Erase the distinctClause if the result is unique by
 definition

I forgot to mention this in the last round of comments. Your patch was actually removing distictClause from the Query structure. Please avoid doing that. If you remove it, you are also removing the evidence that this Query had a DISTINCT clause in it.

Yes, I removed it because it is the easiest way to do it.  what is the purpose of keeping the evidence?

Julien's example provides an explanation for this. The Query structure is serialised into a view definition. Removing distinctClause from there means that the view will never try to produce unique results.
 
 

Suppose after a DDL, the prepared statement need to be re-parsed/planned 
if it is not executed or it will prevent the DDL to happen.  

The query will be replanned. I am not sure about reparsed though.
 


-- session 2
postgres=# alter table t alter column b drop not null;
ALTER TABLE

-- session 1:
postgres=# explain execute st(1);
                         QUERY PLAN
-------------------------------------------------------------
 Unique  (cost=1.03..1.04 rows=1 width=4)
   ->  Sort  (cost=1.03..1.04 rows=1 width=4)
         Sort Key: b
         ->  Seq Scan on t  (cost=0.00..1.02 rows=1 width=4)
               Filter: (c = $1)
(5 rows)

Since this prepared statement is parameterised PostgreSQL is replanning it every time it gets executed. It's not using a stored prepared plan. Try without parameters. Also make sure that a prepared plan is used for execution and not a new plan.
--
Best Wishes,
Ashutosh Bapat

pgsql-hackers by date:

Previous
From: Ashutosh Bapat
Date:
Subject: Re: [PATCH] Erase the distinctClause if the result is unique by definition
Next
From: Justin Pryzby
Date:
Subject: Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace onthe fly