Re: totally different plan when using partitions

Scara Maccai wrote:
> Thank you for your reply. This makes partitions unusable for me...
> hope someone explains why this happens... this still looks like a bug
> to me... BTW the problem arises when adding the second "left outer
> join": when using only 1 partitioned table (that is, only 1 "left
> outer join") the 2 plans are identical.
>
> My answers follow.
>
>> Well, the first version was expecting about 400 rows, the second
>> 15000, so it's not surprising that they have different plans.
>
> Well, they're using exactly the same tables; I don't understand why
> one expects 400 rows and the other 15000....

Well let's see. It's difficult to be certain without the table
definitions and sizes, but...

Both sequentially scan "cell_bsc_60_0610" because it will return < 300
rows. Seems sensible, and it gets the row estimate right.

The fast one then uses the index "teststscell73_0610_1_pkey" to probe
for matches and then again via "teststscell13_0610_1_pkey". Two nested
loops only make sense where you have a small number of rows matching,
otherwise it can get expensive going back and fore to the index and
table all the time. In this case, we end up matching more rows than we
expected (rows=60 loops=285 gives us ~17100 rows to check against the
second index) but not enough to slow us down.

That first plan estimated a cost of 33391 for its 408 rows and the
second 70402 for 15982 rows. That's double the estimated cost, but it
takes four times longer to complete. If the first query had estimated
the number of rows correctly it would have *looked* more expensive than
the second. So - with your particular setup PostgreSQL thinks it takes
longer to do index searches than it really does (or perhaps it thinks
sorting is quicker, or both).

You might want to adjust your cost estimates (see ch 18.6 of the
manuals). Start by checking effective_cache_size. Then maybe reduce
random_page_cost. Small steps though - these settings will affect all
your other queries too.

Also if you have the time, try issuing "set enable_mergejoin=off" and
re-issue query #2. That will force it to choose another plan.

Oh - if the partitions hold historical (unchanging) data it might be
worth CLUSTERing them on the pkey index too.

>> 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.
>
> Sorry, didn't understand that...

The partitioning relies on proving that only partition P needs to be
considered. The more complicated the situation the harder it is to do
that. In this case, I'm not sure that's whats happening though - it
seems to narrow the partition down well enough.

>> 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.
>
> Data is partitioned on a "4 tables per month" basis

But that's from the start of the 1st to the start of the 6th - five full
days rather than 7+. It also *includes* the start of the 6th (<= not <)
which suggests overlap on the partitions. What does the definition of
your partition say *exactly*?

--
Richard Huxton
Archonet Ltd