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

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

>
>
> On Tue, Feb 11, 2020 at 12:22 AM Ashutosh Bapat <
> ashutosh(dot)bapat(dot)oss(at)gmail(dot)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