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
You don't have any indexes on the 'customerclass' table.
Creating a foreign key doesn't create an index, you need to do that
create index cc_customerid_class on indextest.customerclass(classid,
Postgresql & php tutorials
In response to
pgsql-performance by date
|Next:||From: Chris||Date: 2007-07-24 08:50:55|
|Subject: Re: Query performance issue|
|Previous:||From: Jonathan Gray||Date: 2007-07-24 07:48:07|
|Subject: Query performance issue|