[PERFORM] way to speed up a SELECT DISTINCT?
Hello, I am running 7.3.2 RPMs on RH9, on a celeron 1.7 w/ 1gig ram. I have a table that has 6.9 million rows, 2 columns, and an index on each column. When I run: SELECT DISTINCT column1 FROM table It is very, very slow (10-15 min to complete). An EXPLAIN shows no indexes are being used. Is there any way to speed this up, or is that DISTINCT going to keep hounding me? I checked the mailing list, and didn't see anything like this. Any tips or hints would be greatly appreciated. Thanks for your help! Seth ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] way to speed up a SELECT DISTINCT?
Is there any way to speed this up, or is that DISTINCT going to keep hounding me? I checked the mailing list, and didn't see anything like this. Any tips or hints would be greatly appreciated. Thanks for your help! Seth Try group by instead. I think this is an old bug its fixed in 7.3.2 which I'm using. Peter Childs ` [EMAIL PROTECTED]:express=# explain select distinct region from region; QUERY PLAN --- --- Unique (cost=0.00..4326.95 rows=9518 width=14) -> Index Scan using regionview_region on region (cost=0.00..4089.00 rows=95183 width=14) (2 rows) Thanks for the tip, I'll give this a shot soon. I am curious, your example above does not use GROUP BY yet you have an INDEX SCAN. I am using a similar query, yet I get a full table scan. I wonder how they are different? I'll try the group by anyway. Thanks, Seth ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] ways to force index use?
Hello,
Thanks to all the previous suggestions for my previous question. I've
done a lot more research and playing around since then, and luckily I
think I have a better understanding of postgresql.
I still have some queries that don't use an index, and I was wondering
if there were some other tricks to try out?
My system: RH9, PG 7.3.4, IDE, 1 gig RAM, celeron 1.7
My Table Columns (all bigints): start, stop, step1, step2, step3
My Indexes: btree(start), btree(stop), btree(start, stop)
Size of table: 16212 rows
Params: shared_buffers = 128, effective_cache_size = 8192
The Query: explain analyze select * from path where start = 653873 or
start = 649967 or stop = 653873 or stop = 649967
The Result:
Seq Scan on "path" (cost=0.00..450.22 rows=878 width=48) (actual time=0
.08..40.50 rows=1562 loops=1)
Filter: (("start" = 653873) OR ("start" = 649967) OR (stop = 653873) OR
(stop = 649967))
Total runtime: 42.41 msec
Does anyone have a suggestion on how to get that query to use an index?
Is it even possible? I did run vacuum analyze right before this test.
I'm only beginning to customize the parameters in postgresql.conf
(mainly from tips from this list).
Thanks very much!
Seth
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] ways to force index use?
On Monday, Oct 13, 2003, at 21:24 Pacific/Honolulu, mila wrote: Seth, My system: RH9, PG 7.3.4, IDE, 1 gig RAM, celeron 1.7 ... Size of table: 16212 rows Params: shared_buffers = 128, effective_cache_size = 8192 Just in case, the "shared_buffers" value looks a bit far too small for your system. I think you should raise it to at least 1024, or so. Effective cache size could be (at least) doubled, too ==> this might help forcing the index use. Thanks! I'm just beginning to play with these numbers. I'll definitely try them out. I can't wait to try out the script that will help set these parameters! :) Seth ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
