From: Nick Fankhauser <nickf@ontko.com>
To: Richard Poole <richard@ruthie.org>
To: Pgsql-Performance@Postgresql. Org <pgsql-performance@postgresql.org>
Subject: Re: Nested loop performance
Date: Wed, 17 Dec 2003 10:26:25 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGMEPAKBAA.nickf@ontko.com> (raw)
In-Reply-To: <20031216235541.GB8054@guests.deus.net>
> It seems that your basic problem is that you're fetching lots of rows
> from two big ol' tables.
> It doesn't seem to me that there would be a substantially better plan
> for this query with your tables as they stand.
That's more or less the conclusion I had come to. I was just hoping someone
else could point out an approach I've been missing. (sigh!)
> If your data were more
> normalised, then your big scans might be quicker (because their rows
> would be smaller so they would hit fewer disk pages),
This started off as a 5-table join on well-normalized data. Unfortunately,
the actor table doesn't get any smaller, and the work involved in
calculating the "case_count" information on the fly was clearly becoming a
problem- particularly with actors that had a heavy caseload. (Busy attorneys
and judges.) The actor_summary approach makes these previous problem cases
go away, but the payback is that (as you correctly pointed out) queries on
average citizens who only have one case suffer from the de-normalized
approach.
We're currently considering the approach of just returning all of the rows
to our application, and doing the aggregation and limit work in the app. The
inconsistency of the data makes it very tough for the query planner to come
up with an strategy that is always a winner.
Thanks for your thoughts!
-Nick
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: nickf@ontko.com, richard@ruthie.org
Subject: Re: Nested loop performance
In-Reply-To: <NEBBLAAHGLEEPCGOBHDGMEPAKBAA.nickf@ontko.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox