Re: Partitions and joins lead to index lookups on all partitions

Hi,

On 8 December 2011 02:15, Christiaan Willemsen <cwillemsen(at)technocon(dot)com> wrote:
> Currently, we are running into serious performance problems with our
> paritioning setup, because index lookups are mostly done on allpartions, in
> stead of the one partition it should know that it can find the needed row.

Planner is not very smart about partitions. If expression can't be
evaluated to constant (or you use stable/volatile function) during
planning time then you get index/seq scan across all partitions.

> Now when I join the two:
>
> select part_table.* from part_table
>
> join ref_table on (ref_table.part_table_id = part_table.id and group_id =
> 12321)

I had to add extra where conditions which help to decide the right
partitions i.e. where part_col between X and Y. It would be quite hard
to this in your case. You can execute another query like
- select part_table_id from ref_table where group_id = 12321
- or select min(part_table_id), max(part_table_id) from ref_table
where group_id = 12321
and the use in() or between X and Y in second query (so have to
execute two queries).

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)