Performance of outer joins?

From: Benjamin Smith <ben(at)schoolpathways(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Performance of outer joins?
Date: 2006-12-15 23:59:12
Message-ID: 200612151559.12736.ben@schoolpathways.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a situation that can be summarized to the following:

-- day in 20061215 format
Create table calendar (
day integer unique not null
);

Create table customers (
id serial unique not null,
name varchar,
address varchar,
);

Create table deliveries (
customers_id integer not null references customers(id),
calendar_day integer not null references calendar(day),
delivered bool not null default false,
unique(customers_id, calendar_id)
);

Imagine tens of thousands of customers, a few million deliveries. A query
that's structurally similar to the following query is rather slow. It's
taking over 30 seconds, climbing fast on reasonable HW. (SMP Opteron, 10k
SCSI, 4 GB RAM) If I remove the outer join, performance is < 1 second.

SELECT customers.id as customers_id,
customers.name AS customers_name,
calendar.day AS calendar_day,
CASE WHEN (deliveries.delivered IS NULL) THEN 'n/a'
WHEN (deliveries.delivered=TRUE) THEN 'yes'
ELSE 'no' END AS delivered
FROM customers
JOIN calendars ON
(
-- GIVE A CALENDAR OF POSSIBLE DAYS FOR DELIVERIES
calendar.day < 20061201
AND calendar.day >= 20060101
)
LEFT OUTER JOIN deliveries ON
(
customers.id=deliveries.customers_id
AND deliveries.calendar_day=calendar.day
)
;

What can I do to improve the performance of this oft-used query? Is there a
better way to do this, or am I doomed to looping thru results and parsing the
results in code?

Thanks,

-Ben

--
"I kept looking around for somebody to solve the problem.
Then I realized I am somebody"
-Anonymous

Responses

Browse pgsql-general by date

  From Date Subject
Next Message m.c.wilkins 2006-12-16 02:49:47 Re: updating a view
Previous Message Brandon Aiken 2006-12-15 22:54:27 Re: TIMESTAMP WITHOUT TIME ZONE