Skip site navigation (1) Skip section navigation (2)

Re: increase index performance

From: Thomas Finneid <tfinneid(at)fcon(dot)no>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: Greg Smith <gsmith(at)gregsmith(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: increase index performance
Date: 2009-05-13 19:45:28
Message-ID: 4A0B2358.4020006@fcon.no (view raw or flat)
Thread:
Lists: 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

In response to

Responses

pgsql-performance by date

Next:From: Dimitri FontaineDate: 2009-05-13 21:23:15
Subject: Re: Any better plan for this query?..
Previous:From: Scott CareyDate: 2009-05-13 18:59:00
Subject: Re: AMD Shanghai versus Intel Nehalem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group