Re: How can i monitor exactly what (partition) tables are accessed by a query?

On 19 September 2014 09:13, David Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>> What may be worth a try is to join against a UNION ALL of your partitions,
>> with each section of the UNION having an explicirt WHERE clause matching
>> your partitioning constraints.
>> The idea there is that such a UNION could provide the explicit constant
>> WHERE clauses that your JOIN implicitly depends on.
>
>
> That makes no sense. If you join against partitions instead of the parent
> then the contents of the where clause on those partition queries is
> irrelevant.

Perhaps. That depends on whether the planner will use the information
that the CHECK constraints in those partition tables provide; it
probably does, though.
Admittedly, a combination of E_NOCOFFEE and E_NOTENOUGHTIME caused me
to mix up the master table (for which that makes a lot more sense) and
the underlying partition tables there.

> Furthermore, combining a bunch of of queries via union is
> exactly what PostgreSQL is doing when it executes the original plan - it's
> just you are doing it manually.

Ah yes, it does. It's really the distribution of those same values in
the other side of the join that's causing the planning issues.

I suppose you could attempt to manually "partition" the left-hand side
of the join. That sounds like a recipe to end up with a really
inefficient query though - can the planner do that and did it discard
the option because it would cost more?

I'm just throwing around some ideas, whether they actually work for
you remains to be seen.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.