Re: increase index performance

From: Thomas Finneid
Subject: Re: increase index performance
Date: ,
Msg-id: 4A0B2358.4020006@fcon.no
(view: Whole thread, Raw)
In response to: Re: increase index performance  (Matthew Wakeling)
Responses: Re: increase index performance  ("Ow Mun Heng")
List: pgsql-performance

Tree view

increase index performance  (Thomas Finneid, )
 Re: increase index performance  (Greg Smith, )
  Re: increase index performance  (Thomas Finneid, )
  Re: increase index performance  (Matthew Wakeling, )
   Re: increase index performance  (Thomas Finneid, )
    Re: increase index performance  ("Ow Mun Heng", )
     Re: increase index performance  (Matthew Wakeling, )
      Re: increase index performance  ("Ow Mun Heng", )

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:

From: Dimitri Fontaine
Date:
Subject: Re: Any better plan for this query?..
From: Arjen van der Meijden
Date:
Subject: Re: AMD Shanghai versus Intel Nehalem