pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex not used when using function
2+ messages / 2 participants
[nested] [flat]
* index not used when using function
@ 2004-09-29 19:41 Shiar <postgres@shiar.org>
0 siblings, 1 reply; 2+ messages in thread
From: Shiar @ 2004-09-29 19:41 UTC (permalink / raw)
To: pgsql-performance
Hi all, a small question:
I've got this table "songs" and an index on column artist. Since there's about
one distinct artist for every 10 rows, it would be nice if it could use this
index when counting artists. It doesn't however:
lyrics=> EXPLAIN ANALYZE SELECT count(DISTINCT artist) FROM songs;
Aggregate (cost=31961.26..31961.26 rows=1 width=14) (actual time=808.863..808.864 rows=1 loops=1)
-> Seq Scan on songs (cost=0.00..31950.41 rows=4341 width=14) (actual time=26.801..607.172 rows=25207 loops=1)
Total runtime: 809.106 ms
Even with enable_seqscan to off, it just can't seem to use the index. The same
query without the count() works just fine:
lyrics=> EXPLAIN ANALYZE SELECT DISTINCT artist FROM songs;
Unique (cost=0.00..10814.96 rows=828 width=14) (actual time=0.029..132.903 rows=3280 loops=1)
-> Index Scan using songs_artist_key on songs (cost=0.00..10804.11 rows=4341 width=14) (actual time=0.027..103.448 rows=25207 loops=1)
Total runtime: 135.697 ms
Of course I can just take the number of rows from the latter query, but I'm
still wondering why it can't use indexes with functions.
Thanks
--
Shiar - http://www.shiar.org
> Faktoj estas malamik del verajh
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: index not used when using function
@ 2004-10-03 15:29 Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
parent: Shiar <postgres@shiar.org>
0 siblings, 0 replies; 2+ messages in thread
From: Pierre-Frédéric Caillaud @ 2004-10-03 15:29 UTC (permalink / raw)
To: Shiar <postgres@shiar.org>; pgsql-performance
Maybe add an order by artist to force a groupaggregate ?
> Hi all, a small question:
>
> I've got this table "songs" and an index on column artist. Since
> there's about
> one distinct artist for every 10 rows, it would be nice if it could use
> this
> index when counting artists. It doesn't however:
>
> lyrics=> EXPLAIN ANALYZE SELECT count(DISTINCT artist) FROM songs;
> Aggregate (cost=31961.26..31961.26 rows=1 width=14) (actual
> time=808.863..808.864 rows=1 loops=1)
> -> Seq Scan on songs (cost=0.00..31950.41 rows=4341 width=14)
> (actual time=26.801..607.172 rows=25207 loops=1)
> Total runtime: 809.106 ms
>
> Even with enable_seqscan to off, it just can't seem to use the index.
> The same
> query without the count() works just fine:
>
> lyrics=> EXPLAIN ANALYZE SELECT DISTINCT artist FROM songs;
> Unique (cost=0.00..10814.96 rows=828 width=14) (actual
> time=0.029..132.903 rows=3280 loops=1)
> -> Index Scan using songs_artist_key on songs (cost=0.00..10804.11
> rows=4341 width=14) (actual time=0.027..103.448 rows=25207 loops=1)
> Total runtime: 135.697 ms
>
> Of course I can just take the number of rows from the latter query, but
> I'm
> still wondering why it can't use indexes with functions.
>
> Thanks
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-10-03 15:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-29 19:41 index not used when using function Shiar <postgres@shiar.org>
2004-10-03 15:29 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.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