increase index performance

From: Thomas Finneid
Subject: increase index performance
Date: ,
Msg-id: 4A09EF97.8070202@fcon.no
(view: Whole thread, Raw)
Responses: Re: increase index performance  (Greg Smith)
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", )

Hi

have the following table (theoretical)

table apartment_location (

    city_id       int,
    street_id  int,
    house_id   int,
    floor_id   int,
    owner       string
    ...
)

index .. ( city_id, street_id, house_id, floor_id ) tablespc indexspace;

on a database with 260 GB of data and an index size of 109GB on separate
raid disks. there are
    85 city_ids, 2000
    street_ids per city,
    20 house_ids per street per city
    5 floor_ids per house_ per street per city

Then I perform a query to retrieve all house_ids for a specified city,
house and floor ( a bit contrived, but the same cardinality applies)

   select street_id, floor_id from apartment_location where
    city_id = 67 and
    house_id = 6 and
    floor_id = 4

this returns about 2000 rows, but the query takes 3-4 seconds. It
performas an index scan, and everything happens inside 6GB of memory.

So the question, any suggestions on how to possibly decrease the query
time. From iostat etc. its seems that most of the work is reading the
index, reading the data takes almost next to nothing.

Any suggestions?

regards

thomas








pgsql-performance by date:

From: Scott Marlowe
Date:
Subject: Re: AMD Shanghai versus Intel Nehalem
From: Thomas Finneid
Date:
Subject: raid setup for db