Re: Delay locking partitions during query execution

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.

> 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.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services