Re: Slow query

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Nicholas Wieland
Sent: Thursday, August 09, 2012 11:47 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Slow query

Hi, I've tried to post on stackoverflow, but nobody is apparently able to
help me.
I'm not going to repeat everything here, there's quite some code in there
that is nicely formatted, but if this is a problem I can repost it in here.

http://stackoverflow.com/questions/11865504/postgresql-slow-query-with-activ
erecord

What seems incredibly strange to me is that postgres is not using the
indexes I've set.

Someone has any idea?

TIA,
  Ngw

============================================================================

The sequential scans are occurring very quickly so that is likely not an
issue. As others have said INDEXes are only used if they are going to be
significantly faster than other actions (like sequential scans). Since your
only filter is the IS NOT NULL it is likely the bulk (or all) of each table
is going to have to be read in anyway so using an index ends up performing
worse than a sequential scan.

It is hard to estimate but the 22ms response for 15,000 records doesn't seem
that terrible.

You seem to be confused regarding how the different types of JOINs work. By
adding a "IS NOT NULL" condition to the RIGHT side of a LEFT JOIN you
effectively negate the OUTER part of the join and turn it into a simple
INNER JOIN - which is best done explicitly.

{

Provinces LEFT OUTER JOIN Facilities ON (...)
WHERE Facilities IS NOT NULL

Is equivalent to

Provinces INNER JOIN Facilities ON (...)

}

If you want to "find all provinces that have facilities" the natural order
of the query should go:

SELECT ...
FROM facilities -- I care only about things that have facilities
INNER JOIN municipalities -- I need this to like back to provinces
INNER JOIN provinces -- and now I have province data for those provinces
with facilities

David J.