Re: Delay locking partitions during query execution - Mailing list pgsql-hackers

On 1/3/19 11:57 PM, David Rowley wrote:
> On Fri, 4 Jan 2019 at 11:48, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>> Nope, that doesn't seem to make any difference :-( In all cases the
>> resulting plan (with 10k partitions) looks like this:
>>
>> test=# explain analyze select * from hashp where a = 13442;
>>
>>                               QUERY PLAN
>> -----------------------------------------------------------------------
>>  Append  (cost=0.00..41.94 rows=13 width=4)
>>          (actual time=0.018..0.018 rows=0 loops=1)
>>    ->  Seq Scan on hashp6784 (cost=0.00..41.88 rows=13 width=4)
>>                              (actual time=0.017..0.018 rows=0 loops=1)
>>          Filter: (a = 13442)
>>  Planning Time: 75.870 ms
>>  Execution Time: 0.471 ms
>> (5 rows)
>>
>> and it doesn't change (the timings on shape) no matter how I set any of
>> the GUCs.
> 
> For this to work, run-time pruning needs to take place, so it must be
> a PREPAREd statement.
> 
> With my test I used:
> 
> bench.sql:
> \set p_a 13315
> select * from hashp where a = :p_a;
> 
> $ pgbench -n -f bench.sql -M prepared -T 60 postgres
> 
> You'll know you're getting a generic plan when you see "Filter (a =
> $1)" and see "Subplans Removed: 9999" below the Append.
> 

Indeed, with prepared statements I now see some improvements:

    partitions    0      100     1000    10000
    --------------------------------------------
    master       19     1590     2090      128
    patched      18     1780     6820     1130

So, that's nice. I wonder why the throughput drops so fast between 1k
and 10k partitions, but I'll look into that later.

Does this mean this optimization can only ever work with prepared
statements, or can it be made to work with regular plans too?

>> Furthermore, I've repeatedly ran into this issue:
>>
>> test=# \d hashp
>> ERROR:  unrecognized token: "false"
>> LINE 2: ...catalog.array_to_string(array(select rolname from pg_catalog...
>>                                                              ^
>> I have no idea why it breaks like this, and it's somewhat random (i.e.
>> not readily reproducible). But I've only ever seen it with this patch
>> applied.
> 
> You'll probably need to initdb with the patch applied as there's a new
> field in RangeTblEntry. If there's a serialised one of these stored in
> the in the catalogue somewhere then the new read function will have
> issues reading the old serialised format.
> 

D'oh! That explains it, because switching from/to patched binaries might
have easily been triggering the error. I've checked that there are no
changes to catalogs, but it did not occur to me adding a new RTE field
could have such consequences ...

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services