pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTwo Index Questions
10+ messages / 4 participants
[nested] [flat]
* Two Index Questions
@ 2002-07-19 16:33 Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 10+ messages in thread
From: Josh Berkus @ 2002-07-19 16:33 UTC (permalink / raw)
To: pgsql-sql
Folks (esp. Tom, Stephan, and Bruce):
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.
DESC Alpha Sort Indexes:
Q: In PostgreSQL 7.0, there was an issue that indexes where never consulted
for DESC alpha sorts. Has this been resolved? If so, does one need to
create any special indexes to take advantage of indexes for DESC sorts?
Thanks for your help!
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Two Index Questions
@ 2002-07-19 16:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2002-07-19 16:44 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
> 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?
An indexscan can only use one index, so separate indexes would be
completely worthless for this query. An index on A,B,C is potentially
useful, but in most cases I think the planner will prefer an explicit
sort anyway if there's no WHERE clause.
> Q: In PostgreSQL 7.0, there was an issue that indexes where never consulted
> for DESC alpha sorts. Has this been resolved? If so, does one need to
> create any special indexes to take advantage of indexes for DESC sorts?
Yes; no.
regression=# create table foo (f1 text);
CREATE TABLE
regression=# create index fooi on foo(f1);
CREATE INDEX
regression=# explain select * from foo order by f1;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using fooi on foo (cost=0.00..52.00 rows=1000 width=32)
(1 row)
regression=# explain select * from foo order by f1 desc;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan Backward using fooi on foo (cost=0.00..52.00 rows=1000 width=32)
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Two Index Questions
@ 2002-07-19 17:00 Bruno Wolff III <bruno@wolff.to>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Bruno Wolff III @ 2002-07-19 17:00 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Fri, Jul 19, 2002 at 09:33:59 -0700,
Josh Berkus <josh@agliodbs.com> wrote:
> Folks (esp. Tom, Stephan, and Bruce):
>
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Two Index Questions
@ 2002-07-19 17:28 Josh Berkus <josh@agliodbs.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 10+ messages in thread
From: Josh Berkus @ 2002-07-19 17:28 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Two Index Questions
@ 2002-07-19 17:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2002-07-19 17:53 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
> I generally try to avoid using LIMIT, as it is a non-SQL92 extension.
Fair enough ...
> Also,
> LIMIT in subqueries might someday be disallowed as it interferes with the
> fundmentally unordered nature of subqueries.
I don't think so; we went out of our way to make it work, so we're
unlikely to discard the feature. It's true that it does prevent some
optimizations that might otherwise occur, but not using it is a
sufficient answer to that.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Two Index Questions
@ 2002-07-19 17:56 Bruno Wolff III <bruno@wolff.to>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Bruno Wolff III @ 2002-07-19 17:56 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Fri, Jul 19, 2002 at 10:28:18 -0700,
Josh Berkus <josh@agliodbs.com> wrote:
>
> 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.
Typo. I meant to say columns. The issue is that max doesn't use an index,
but if there are a lot of different values of G for a given A, B and C,
it may be better to use an index then to search through the applicable
rows to find the maximum.
> 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.
I can see not using it because it is nonstandard. I don't think it would be
disabled for the reason you have given. The documentation makes it pretty
clear that you need to use order by if you want a particular tuple.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Two Index Questions
@ 2002-07-19 18:12 Josh Berkus <josh@agliodbs.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 10+ messages in thread
From: Josh Berkus @ 2002-07-19 18:12 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
Bruno, Tom,
> Typo. I meant to say columns. The issue is that max doesn't use an index,
> but if there are a lot of different values of G for a given A, B and C,
> it may be better to use an index then to search through the applicable
> rows to find the maximum.
That's odd ... you're correct. Tom, why doesn't MAX() use an index? I
understand why indexes are generally useless for SUM(), AVG, and COUNT, but
it seems that MAX() and MIN() should *always* use an index.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Two Index Questions
@ 2002-07-19 18:27 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2002-07-19 18:27 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
Josh Berkus wrote:
> Bruno, Tom,
>
> > Typo. I meant to say columns. The issue is that max doesn't use an index,
> > but if there are a lot of different values of G for a given A, B and C,
> > it may be better to use an index then to search through the applicable
> > rows to find the maximum.
>
> That's odd ... you're correct. Tom, why doesn't MAX() use an index? I
> understand why indexes are generally useless for SUM(), AVG, and COUNT, but
> it seems that MAX() and MIN() should *always* use an index.
Index access methods don't know about aggregates, and our type system
makes such linkage difficult. The FAQ does have:
However, <SMALL>LIMIT</SMALL> combined with <SMALL>ORDER BY</SMALL>
often will use an index because only a small portion of the table
is returned. In fact, though MAX() and MIN() don't use indexes,
it is possible to retrieve such values using an index with ORDER BY
and LIMIT:
<PRE>
SELECT col
FROM tab
ORDER BY col [ DESC ]
LIMIT 1
</PRE>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Two Index Questions
@ 2002-07-19 18:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2002-07-19 18:35 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
> Tom, why doesn't MAX() use an index?
Don't tell me you haven't seen that discussed many times before :-(
Because Postgres uses an extensible set of aggregate functions, we treat
all aggregates as "black boxes": the implementation strategy is always
to pass all the specified values through the aggregate.
Special-casing MIN and MAX would be nice from the point of view of
performance, but there's this little problem that our sets of datatypes
and index types are also extensible. We'd need to devise some
non-hard-wired way of identifying which aggregate functions are related
to the sort orderings of what indexes.
Finally, the transformation into an optimized form is just not that easy
to do automatically in the general case --- it's easy enough if you
write "SELECT max(col) FROM tab", but how about "SELECT max(col),
min(col) FROM tab"? What if there are WHERE clauses (with or without
constraints on col)? And the GROUP BY case that we started this
discussion with is *very* nontrivial.
This issue is on the TODO list, but given that the ORDER BY/LIMIT
workaround is available (and offers more functionality than MAX/MIN
anyway), I don't think it's a very high-priority problem. We've got
plenty of TODO items for which there is no good workaround...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Two Index Questions
@ 2002-07-19 18:58 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2002-07-19 18:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
Tom, Bruce, Bruno
> Don't tell me you haven't seen that discussed many times before :-(
Actually, I'll have to admint to not having read the relevant discussions.
Honestly, on this list I usually only answer the questions I asked or the
questions I'm going to respond to. Short-sighted, but I can't keep up
otherwise.
The explanation certainly makes sense. It just goes against the grain for me
to use a non-SQL standard structure in major parts of my database schema.
But I'll learn to live with it.
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2002-07-19 18:58 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-19 16:33 Two Index Questions Josh Berkus <josh@agliodbs.com>
2002-07-19 16:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-07-19 17:00 ` Bruno Wolff III <bruno@wolff.to>
2002-07-19 17:28 ` Josh Berkus <josh@agliodbs.com>
2002-07-19 17:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-07-19 17:56 ` Bruno Wolff III <bruno@wolff.to>
2002-07-19 18:12 ` Josh Berkus <josh@agliodbs.com>
2002-07-19 18:27 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-19 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-07-19 18:58 ` Josh Berkus <josh@agliodbs.com>
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