From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Nicholas Wieland Sent: Thursday, August 09, 2012 11:47 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
