From: Josh Berkus <josh@agliodbs.com>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Two Index Questions
Date: Fri, 19 Jul 2002 10:28:18 -0700
Message-ID: <200207191028.18547.josh@agliodbs.com> (raw)
In-Reply-To: <20020719170000.GB13657@wolff.to>
References: <200207190933.59283.josh@agliodbs.com>
<20020719170000.GB13657@wolff.to>
Bruno,
> > I have two questions for my "Adventures in PostgreSQL" article reasearch:
> >
> > Multi-Column Indexes and GROUP BY:
> > Q: If you group a table by multiple colums, e.g.
> > SELECT t1.A, t1.B, t1.C, MAX(t1.G)
> > FROM t1
> > GROUP BY t1.A, t1.B, t1.C
> > Then would a multi-column index on A, B, C be faster than seperate
indexes
> > on A, B and C? I've run a few tests, but I don't have enough data in the
> > seperate tables to really get a feel for the difference.
>
> If there are lots of G entries for fixed As, Bs and Cs, then another option
> would be to have an index on all 4 tables and use a subquery with a limit 1
> clause to get the row with the max G value for any A, B and C.
We're talking about only one table, here. Not four.
I generally try to avoid using LIMIT, as it is a non-SQL92 extension. Also,
LIMIT in subqueries might someday be disallowed as it interferes with the
fundmentally unordered nature of subqueries.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
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-sql@postgresql.org
Cc: josh@agliodbs.com, bruno@wolff.to
Subject: Re: Two Index Questions
In-Reply-To: <200207191028.18547.josh@agliodbs.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