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

Re: Query performance issue

From: Chris <dmagick(at)gmail(dot)com>
To: Jonathan Gray <jgray(at)streamy(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query performance issue
Date: 2007-07-24 08:50:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Chris wrote:
> Jonathan Gray wrote:
>> We’re experiencing a query performance problem related to the planner 
>> and its ability to perform a specific type of merge.
>> We have created a test case (as attached, or here: 
>> which involves a 
>> hypothetical customer ordering system, with customers, orders, and 
>> customer groups.
>> If we want to retrieve a single customers 10 most recent orders, 
>> sorted by date, we can use a double index on (customer,date); 
>> Postgres’s query planner will use the double index with  a backwards 
>> index scan on the second indexed column (date).
>> However, if we want to retrieve a “customer class’s” 10 most recent 
>> orders, sorted by date, we are not able to get Postgres to use double 
>> indexes.
> You don't have any indexes on the 'customerclass' table.
> Creating a foreign key doesn't create an index, you need to do that 
> separately.
> Try
> create index cc_customerid_class on indextest.customerclass(classid, 
> customerid);

It could also be that since you don't have very much data (10,000) rows 
- postgres is ignoring the indexes because it'll be quicker to scan the 

If you bump it up to say 100k rows, what happens?

Postgresql & php tutorials

In response to


pgsql-performance by date

Next:From: Jonathan GrayDate: 2007-07-24 09:18:53
Subject: Re: Query performance issue
Previous:From: ChrisDate: 2007-07-24 08:44:04
Subject: Re: Query performance issue

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