Thread: Slow query

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Nicholas Wieland
Sent: Thursday, August 09, 2012 11:47 AM
To: pgsql-general@postgresql.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.

Nicholas Wieland <ngw@nofeed.org> wrote:

> 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.

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

Help you with what?  You're getting 14,511 rows with 54 columns each
resulting from the join of three tables in 22 ms.  What would seem
like a reasonable time to you?

As I explained on SO yesterday, it can be faster to scan the table
and build up a RAM table for fast lookup than to grovel through an
index tens of thousands of times.  If you think the index would be
faster, test that by setting enable_seqscan and enable_hashjoin to
off on your connection and try the EXPLAIN ANALYZE.  I bet you get
the plan you thought you wanted, and I bet it's slower.

-Kevin