Re: increase index performance - Mailing list pgsql-performance

From Matthew Wakeling
Subject Re: increase index performance
Date
Msg-id alpine.DEB.2.00.0905131146550.2341@aragorn.flymine.org
Whole thread Raw
In response to Re: increase index performance  (Greg Smith <gsmith@gregsmith.com>)
Responses Re: increase index performance
List pgsql-performance
On Tue, 12 May 2009, Greg Smith wrote:
> You should test what happens if you reduce the index to just being
> (city_id,street_id).

I think you're missing the point a little here. The point is that Thomas
is creating an index on (city_id, street_id, house_id, floor_id) and
running a query on (city_id, house_id, floor_id).

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.

Matthew

--
 An ant doesn't have a lot of processing power available to it. I'm not trying
 to be speciesist - I wouldn't want to detract you from such a wonderful
 creature, but, well, there isn't a lot there, is there?
                                        -- Computer Science Lecturer

pgsql-performance by date:

Previous
From: Dimitri
Date:
Subject: Re: Any better plan for this query?..
Next
From: Stefan Kaltenbrunner
Date:
Subject: Re: PostgreSQL with PostGIS on embedded hardware