Re: increase index performance

From: Matthew Wakeling
Subject: Re: increase index performance
Date: ,
Msg-id: alpine.DEB.2.00.0905131146550.2341@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Re: increase index performance  (Greg Smith)
Responses: Re: increase index performance  (Thomas Finneid)
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", )

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:

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