Re: Partitioning

[François Beausoleil <francois@xxxxxxxxxxx>]

Le 2015-01-18 à 20:58, James Sewell <james.sewell@xxxxxxxxxxxx> 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.

Hope that helps!

François Beausoleil