Re: pgsql: Support partition pruning at execution time

On 8 April 2018 at 09:57, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
>> Support partition pruning at execution time
>
> Buildfarm member lapwing doesn't like this. I can reproduce the
> failures here by setting force_parallel_mode = regress. Kind
> of looks like instrumentation counts aren't getting propagated
> from workers back to the leader?

I'm looking at this now. I've tried adding vacuum (analyze) to the
tables before the queries in order to have relallvisible set so that
the index only scan's "Heap Fetches" becomes stable, but very weirdly
it still sometimes fetches from the heap after having vacuumed.

To help see what's going on while testing this I added:

select relname,relallvisible from pg_Class where relname like 'tprt%'
and relkind = 'r';

just before the:

explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 > tprt.col1;

Sometimes I see:

relname | relallvisible
---------+---------------
tprt_1 | 0
tprt_2 | 1

Other times I see:

relname | relallvisible
---------+---------------
tprt_1 | 0
tprt_2 | 0

I thought maybe something might be holding a pin on a page somewhere
and vacuum could be skipping it, so I added a VERBOSE to the vacuum
and I see:

Skipped 0 pages due to buffer pins, 0 frozen pages.

I'd considered just doing: set enable_indexonly_scan = off; for all
these tests, but I don't have an explanation for this vacuum behaviour
yet.

I'll need to dig through the vacuum code that sets the visibility bit
and see if there's some good reason for this. I have a local patch
ready to go for the set enable_indexonlyscan = off;

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