On Wed, Jul 15, 2009 at 8:51 AM, Alex<alex@xxxxxxxxxx> wrote: > Also posted this to the list. Thanks for your answer - still > struggling. Staying on-list is always preferred. >> How is the index sl_city_etc defined? > > Index "public.sl_city_etc" > Column | Type > --------------+----------------------------- > city | text > listing_type | text > post_time | timestamp without time zone > bedrooms | integer > region | text > geo_lat | integer > geo_lon | integer > btree, for table "public.source_listings" So the presence of listing_type before post_time when it's not in your query means that the index scan has to look at every entry for 'boston'. It skips over entries that don't match the post_time or geo columns but it still has to go through them in the index. Think of being asked to find every word in the dictionary starting with 'a' and whose third letter is 'k' but with no restriction on the second letter... You would probably be better off starting with separate indexes on each column and then considering how to combine them if possible than starting with them all in one index like this. If you always have city in your query and then some collection of other columns then you could have separate indexes on <city,listing_type>, <city,post_time>, <city, bedrooms>, etc. The geometric columns are a more interesting case. You could have separate indexes on each and hope a bitmap scan combines them, or you could use a geometric GIST index on point(geo_lon,geo_lat). Doing so would mean using the right operator to find points within a box rather than simple < and > operators. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
