pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedgroup by will not use an index?
7+ messages / 6 participants
[nested] [flat]
* group by will not use an index?
@ 2007-01-09 23:05 tsuraan <tsuraan@gmail.com>
2007-01-09 23:32 ` Re: group by will not use an index? Adam Rich <adam.r@sbcglobal.net>
2007-01-09 23:42 ` Re: group by will not use an index? Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 2 replies; 7+ messages in thread
From: tsuraan @ 2007-01-09 23:05 UTC (permalink / raw)
To: pgsql-performance
I have a table of messages with paths and inserted dates (among other
things), like so:
CREATE TABLE Messages (
msgkey BIGSERIAL PRIMARY KEY,
path TEXT NOT NULL,
inserted TIMESTAMP WITHOUT TIMEZONE DEFAULT NOW()
);
I run a query to determine which days actually saw emails come in, like so:
SELECT DATE(inserted) FROM Messages GROUP BY DATE(inserted);
That's obviously not very efficient, so I made an index:
CREATE INDEX messages_date_inserted_ind ON Messages(DATE(inserted));
However, GROUP BY does not use this index:
=# explain analyze select date(inserted) from messages group by
date(inserted);
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=104773.10..104789.51 rows=1313 width=8) (actual time=
31269.476..31269.557 rows=44 loops=1)
-> Seq Scan on messages (cost=0.00..101107.25 rows=1466340 width=8)
(actual time=23.923..25248.400 rows=1467036 loops=1)
Total runtime: 31269.735 ms
(3 rows)
Is it possible to get pg to use an index in a group by? I don't see why it
wouldn't be possible, but maybe I'm missing something.
Using pg 8.1.4...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: group by will not use an index?
2007-01-09 23:05 group by will not use an index? tsuraan <tsuraan@gmail.com>
@ 2007-01-09 23:32 ` Adam Rich <adam.r@sbcglobal.net>
1 sibling, 0 replies; 7+ messages in thread
From: Adam Rich @ 2007-01-09 23:32 UTC (permalink / raw)
To: 'tsuraan' <tsuraan@gmail.com>; pgsql-performance
That query looks strange to me (a group by without an aggregate). See
if this is
any faster:
SELECT DISTINCT DATE(inserted) FROM Messages
I won't hold my breath though, I don't think there's any way around the
full table scan
in Postgres, because the index does not contain enough information about
transactional
state, so table access is always required (unlike virtually every other
type of db)
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of tsuraan
Sent: Tuesday, January 09, 2007 5:06 PM
To: pgsql-performance
Subject: [PERFORM] group by will not use an index?
I have a table of messages with paths and inserted dates (among other
things), like so:
CREATE TABLE Messages (
msgkey BIGSERIAL PRIMARY KEY,
path TEXT NOT NULL,
inserted TIMESTAMP WITHOUT TIMEZONE DEFAULT NOW()
);
I run a query to determine which days actually saw emails come in, like
so:
SELECT DATE(inserted) FROM Messages GROUP BY DATE(inserted);
That's obviously not very efficient, so I made an index:
CREATE INDEX messages_date_inserted_ind ON Messages(DATE(inserted));
However, GROUP BY does not use this index:
=# explain analyze select date(inserted) from messages group by
date(inserted);
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------
HashAggregate (cost=104773.10..104789.51 rows=1313 width=8) (actual
time=31269.476..31269.557 rows=44 loops=1)
-> Seq Scan on messages (cost=0.00..101107.25 rows=1466340 width=8)
(actual time=23.923..25248.400 rows=1467036 loops=1)
Total runtime: 31269.735 ms
(3 rows)
Is it possible to get pg to use an index in a group by? I don't see why
it wouldn't be possible, but maybe I'm missing something.
Using pg 8.1.4...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: group by will not use an index?
2007-01-09 23:05 group by will not use an index? tsuraan <tsuraan@gmail.com>
@ 2007-01-09 23:42 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-01-10 01:41 ` Re: group by will not use an index? Andrew Lazarus <andrew@pillette.com>
1 sibling, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2007-01-09 23:42 UTC (permalink / raw)
To: tsuraan <tsuraan@gmail.com>; +Cc: pgsql-performance
On Tue, 2007-01-09 at 17:05, tsuraan wrote:
> I have a table of messages with paths and inserted dates (among other
> things), like so:
>
> CREATE TABLE Messages (
> msgkey BIGSERIAL PRIMARY KEY,
> path TEXT NOT NULL,
> inserted TIMESTAMP WITHOUT TIMEZONE DEFAULT NOW()
> );
>
> I run a query to determine which days actually saw emails come in,
> like so:
>
> SELECT DATE(inserted) FROM Messages GROUP BY DATE(inserted);
You're probably under the mistaken impression that PostgreSQL and can
retrieve all the data it needs from the index alone. It can't. Anytime
postgresql gets an index reference, it has to then visit the actual
table file to grab the individual entry. That's because indexes don't
store mvcc visibility information, and due to the problems locking both
indexes and tables together would present, probably won't any time soon.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: group by will not use an index?
2007-01-09 23:05 group by will not use an index? tsuraan <tsuraan@gmail.com>
2007-01-09 23:42 ` Re: group by will not use an index? Scott Marlowe <smarlowe@g2switchworks.com>
@ 2007-01-10 01:41 ` Andrew Lazarus <andrew@pillette.com>
2007-01-10 17:14 ` Re: group by will not use an index? tsuraan <tsuraan@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew Lazarus @ 2007-01-10 01:41 UTC (permalink / raw)
To: tsuraan <tsuraan@gmail.com>; +Cc: pgsql-performance
For the reasons indicated (that is, MVCC), PG can not do a DISTINCT or the equivalent
GROUP BY from index values alone.
If this table is large, perhaps you could denormalize and maintain a
summary table with date (using truncation) and count, updated with
triggers on the original table. This table will presumably have a
small number of rows at the cost of doubling the times for updates,
inserts, and deletes.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: group by will not use an index?
2007-01-09 23:05 group by will not use an index? tsuraan <tsuraan@gmail.com>
2007-01-09 23:42 ` Re: group by will not use an index? Scott Marlowe <smarlowe@g2switchworks.com>
2007-01-10 01:41 ` Re: group by will not use an index? Andrew Lazarus <andrew@pillette.com>
@ 2007-01-10 17:14 ` tsuraan <tsuraan@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: tsuraan @ 2007-01-10 17:14 UTC (permalink / raw)
To: Andrew Lazarus <andrew@pillette.com>; +Cc: pgsql-performance
> For the reasons indicated (that is, MVCC), PG can not do a DISTINCT or the
> equivalent
> GROUP BY from index values alone.
Ok, that makes sense. Thanks for the help everybody!
If this table is large, perhaps you could denormalize and maintain a
> summary table with date (using truncation) and count, updated with
> triggers on the original table. This table will presumably have a
> small number of rows at the cost of doubling the times for updates,
> inserts, and deletes.
Well, the inserted time, at least, is never updated, and deletions are very
rare (never, so far), so I'll have a look at doing things that way. Thanks!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: group by will not use an index?
@ 2007-01-10 01:07 Brian Herlihy <btherl@yahoo.com.au>
2007-01-10 01:11 ` Re: group by will not use an index? Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 1 reply; 7+ messages in thread
From: Brian Herlihy @ 2007-01-10 01:07 UTC (permalink / raw)
To: pgsql-performance
Actually, as I recently discovered, GROUP BY is faster than DISTINCT. It's just due to how they are implemented, so don't go looking for any deep reason :) The thread "GROUP BY vs DISTINCT" from 2006-12-20 discusses it. DISTINCT sorts the results to find the unique rows, but GROUP BY uses a hash.
Brian
----- Original Message ----
From: Adam Rich <adam.r@sbcglobal.net>
To: tsuraan <tsuraan@gmail.com>; pgsql-performance <pgsql-performance@postgresql.org>
Sent: Wednesday, 10 January, 2007 7:32:50 AM
Subject: Re: [PERFORM] group by will not use an index?
Message
That
query looks strange to me (a group by without an aggregate). See if this
is
any
faster:
SELECT
DISTINCT DATE(inserted) FROM Messages
I
won't hold my breath though, I don't think there's any way around the full table
scan
in
Postgres, because the index does not contain enough information about
transactional
state,
so table access is always required (unlike virtually every other type of
db)
-----Original Message-----
From:
pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
tsuraan
Sent: Tuesday, January 09, 2007 5:06 PM
To:
pgsql-performance
Subject: [PERFORM] group by will not use an
index?
I have a table of messages with paths and inserted
dates (among other things), like so:
CREATE TABLE Messages
(
msgkey BIGSERIAL PRIMARY KEY,
path TEXT NOT NULL,
inserted TIMESTAMP WITHOUT TIMEZONE
DEFAULT NOW()
);
I run a query to determine which days actually saw
emails come in, like so:
SELECT DATE(inserted) FROM Messages GROUP BY
DATE(inserted);
That's obviously not very efficient, so I made an
index:
CREATE INDEX messages_date_inserted_ind ON
Messages(DATE(inserted));
However, GROUP BY does not use this
index:
=# explain analyze select date(inserted) from messages group by
date(inserted);
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------
HashAggregate
(cost=104773.10..104789.51 rows=1313 width=8) (actual
time=31269.476..31269.557 rows=44 loops=1)
-> Seq
Scan on messages (cost=0.00..101107.25 rows=1466340 width=8) (actual
time=23.923..25248.400 rows=1467036 loops=1)
Total runtime: 31269.735
ms
(3 rows)
Is it possible to get pg to use an index in a group
by? I don't see why it wouldn't be possible, but maybe I'm missing
something.
Using pg 8.1.4...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: group by will not use an index?
2007-01-10 01:07 Re: group by will not use an index? Brian Herlihy <btherl@yahoo.com.au>
@ 2007-01-10 01:11 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 0 replies; 7+ messages in thread
From: Steinar H. Gunderson @ 2007-01-10 01:11 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 09, 2007 at 05:07:03PM -0800, Brian Herlihy wrote:
> Actually, as I recently discovered, GROUP BY is faster than DISTINCT. It's
> just due to how they are implemented, so don't go looking for any deep
> reason :) The thread "GROUP BY vs DISTINCT" from 2006-12-20 discusses it.
> DISTINCT sorts the results to find the unique rows, but GROUP BY uses a
> hash.
Actually, GROUP BY _can_ use a sort too, it's just that a hash is usually
faster.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-01-10 17:14 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-09 23:05 group by will not use an index? tsuraan <tsuraan@gmail.com>
2007-01-09 23:32 ` Adam Rich <adam.r@sbcglobal.net>
2007-01-09 23:42 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-01-10 01:41 ` Andrew Lazarus <andrew@pillette.com>
2007-01-10 17:14 ` tsuraan <tsuraan@gmail.com>
2007-01-10 01:07 Re: group by will not use an index? Brian Herlihy <btherl@yahoo.com.au>
2007-01-10 01:11 ` Steinar H. Gunderson <sgunderson@bigfoot.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