agora inbox for pgsql-performance@postgresql.org  

help / color / mirror / Atom feed

From: Chris <dmagick@gmail.com>
To: Jonathan Gray <jgray@streamy.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance issue
Date: Tue, 24 Jul 2007 18:50:55 +1000
Message-ID: <46A5BD6F.7000108@gmail.com> (raw)
In-Reply-To: <46A5BBD4.8040509@gmail.com>
References: <0f1701c7cdc6$f9cc1a30$ed644e90$@com>
	<46A5BBD4.8040509@gmail.com>

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: 
>> http://www3.streamy.com/postgres/indextest.sql) 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 
tables.

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

-- 
Postgresql & php tutorials
http://www.designmagick.com/

view thread (57+ messages)  latest in thread

Message-ID: <46A5BD6F.7000108@gmail.com>
Permalink:  ../46A5BD6F.7000108@gmail.com/
Also on:    postgresql.org/message-id/46A5BD6F.7000108@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dmagick@gmail.com, jgray@streamy.com
  Subject: Re: Query performance issue
  In-Reply-To: <46A5BD6F.7000108@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox