Re: increase index performance - Mailing list pgsql-performance

From Thomas Finneid
Subject Re: increase index performance
Date
Msg-id 4A0B2358.4020006@fcon.no
Whole thread Raw
In response to Re: increase index performance  (Matthew Wakeling <matthew@flymine.org>)
Responses Re: increase index performance  ("Ow Mun Heng" <ow.mun.heng@wdc.com>)
List pgsql-performance
Matthew Wakeling wrote:
> Thomas, the order of columns in the index matters. The index is
> basically a tree structure, which resolves the left-most column before
> resolving the column to the right of it. So to answer your query, it
> will resolve the city_id, then it will have to scan almost all of the
> tree under that, because you are not constraining for street_id. A much
> better index to answer your query is (city_id, house_id, floor_id) -
> then it can just look up straight away. Instead of the index returning
> 200000 rows to check, it will return just the 2000.

Thats something I was a bit unsure about, because of the cardinality of
the data. But thanks, I will try it. Just need to populate a new data
base with the new index. (Apparently, creating a new index on an already
existing database is slower than just recreating the db, when the db is
250GB big)


thomas

pgsql-performance by date:

Previous
From: Scott Carey
Date:
Subject: Re: AMD Shanghai versus Intel Nehalem
Next
From: Dimitri Fontaine
Date:
Subject: Re: Any better plan for this query?..