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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
