Re: totally different plan when using partitions

Scara Maccai wrote:
>
> same query, but using postgresql's partition pruning ("2"):
>
> explain analyze
> select nome1,
> thv3tralacc,
> dltbfpgpdch
> FROM cell_bsc_60_0610 as cell_bsc
> left outer join teststscell73 as data on data.ne_id=cell_bsc.nome1
> left outer join teststscell13 as data1 on data1.ne_id=cell_bsc.nome1 and data1.time=data.time
> where
>
> data.time >=cell_bsc.starttime and data.time <=cell_bsc.endtime and
> data.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00'
> and data1.time >=cell_bsc.starttime and data1.time <=cell_bsc.endtime
> and data1.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00'
> and cell_bsc.nome2=2
>
>
> explain analyze:
>
> http://explain-analyze.info/query_plans/3807-query-plan-2511
>
>
> The second version is A LOT slower (10x). But the 2 queries should be identical... why the two totally different plans???

Well, the first version was expecting about 400 rows, the second 15000,
so it's not surprising that they have different plans.

I'm not sure whether the planner is smart enough to cope with the
multiple tests on time vs the partitioning and realise it can use your
index on the partition.

I'm assuming the partition is defined as being "between '2006-10-01
00:00:00' and '2006-10-06 00:00:00'" - strikes me as an odd interval,
and you don't say anywhere.

You _might_ have a better plan by moving the partitioned tests into
subqueries:
FROM cell_bsc_60_0610 as cell_bsc
left outer join (
SELECT ne_id FROM teststscell73 WHEREtime between '2006-10-01
00:00:00' and '2006-10-06 00:00:00') as data on data.ne_id=cell_bsc.nome1

--
Richard Huxton
Archonet Ltd