Re: Delay locking partitions during query execution

On 1/3/19 11:57 PM, David Rowley wrote:
> On Fri, 4 Jan 2019 at 11:48, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)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