Lawrence Cohan <LCohan(at)web(dot)com> wrote:
> Please see updated attachment that includes the tables involved in
> the simple query below and all their indexes.
Well, that rules out a couple common problems (comparisons between
different types and incorrect indexing).
> We believe that the performance issue is due to the query not
> using any index but doing seq scans instead
So it appears.
> and this is very little related to the knowledge from the link you
> posted below.
Oh, but it is very much related. The PostgreSQL optimizer looks at
all the various plans available, calculates a cost for each, and run
the one with the lowest calculated cost. Various configuration
parameters affect the costing calculations, and thus the plan
ultimately chosen. To get good plans, the configuration must
accurately model the actual costs for your particular machine.
Having seen that the types match and the indexes look usable, it
must come down to something in your configuration. Probably the
easiest way to show that is to run the query here and post the
results:
http://wiki.postgresql.org/wiki/Server_Configuration
> As you can see we picked a simple query with INNER JOIN between
> two indexed tables where postgres 8.3 and 9.0 decides to not use
> existing indexes for whatever reason.
The reason is that with the configuration you're using, PostgreSQL
calculates the cost of using the index as being higher than the cost
of a sequential scan. The trick is to find where your configuration
is wrong, so that the calculated costs better match the reality on
your server.
-Kevin