Re: Partitioning

Hi,

19 Jan 2015 06:58:21 -0500, François Beausoleil <francois(at)teksol(dot)info> wrote in <CC2FD572-320A-4225-B98C-48F20992527C(at)teksol(dot)info>
>
> > Le 2015-01-18 à 20:58, James Sewell <james(dot)sewell(at)lisasoft(dot)com> a écrit :
> >
> > Hello,
> >
> > I am using partitioning with around 100 sub-tables. Each sub-table is around 11GB and partitioned on the 'id' column. I have an index on the id column on each sub-table.
> >
> > Is it possible to get a query like the following working using constraint exclusion, or am I doomed to do index/sequential scans of every sub-table?
> >
> > I want to select all rows which have an id which is in another query, so something like:
> >
> > WITH idlist as (SELECT id from othertable)
> > SELECT id from mastertable WHERE id = idlist.id);
> >
> > I am guessing that I am not getting constraint exclusion to work as the planner doesn't know the outcome of my subquery at plan time?
> >
> How many rows in idlist? Can you do two queries? Fetch the ids, then call the 2nd query with those values hard-coded in the query.
>
> If necessary, and you know where each partition starts, tou could sort in the app and query the correct ranges, in multiple queries.

The strategy's effectiveness is depends mainly on how many ids
come from the othertable. It wins if fewer than certain number or
converged in a few partitions, however, straight joins will win
elsewise.

The result of EXPLAIN ANALYZE might draw more precise advices.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center