Re: How can I speed up this search?

Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> I am searching on a FULL JOIN of two tables (using 7.2.1). VACUUM
> ANALYZE has been done.

> If I search on either of them separately, the search uses the
> appropriate index, but when they are joined, the second table uses a
> sequential scan and takes far longer than (I think) it ought. Can I
> reshape the query or add additional indexes to improve the planner's
> guess?

I am guessing that the problem has something to do with the fact that
the indexed columns are integer in one table and smallint in the other.
Yet in my tests both 7.2 and current sources can figure out how to do an
indexed mergejoin between integer and smallint columns. Strange that
it's not doing that for you.

Can you try (a) remaking the tables with identical column types;
(b) removing the product_currency_index index to see if the planner
will use the other index when it has no choice?

regards, tom lane