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:44:04
Message-ID: 46A5BBD4.8040509@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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);

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

In response to

Responses

pgsql-performance by date

Next:From: ChrisDate: 2007-07-24 08:50:55
Subject: Re: Query performance issue
Previous:From: Jonathan GrayDate: 2007-07-24 07:48:07
Subject: Query performance issue

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