Re: Poor query performance

On Wed, Jul 15, 2009 at 8:51 AM, Alex<alex(at)liivid(dot)com> 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