From: | "Fred Moyer" <fred(at)digicamp(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Cc: | <fred(at)digicamp(dot)com> |
Subject: | Query optimization |
Date: | 2002-12-07 02:16:43 |
Message-ID: | 64850.168.103.211.137.1039227403.squirrel@mail.digicamp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Greetings!
I am trying to find a way to optimize this query and have hit a wall. The
database size is 2.9 GB and contains 1 million records. The system is a
dual xeon 1 ghz P3 with 4 GB ram, 2 of it shared memory. Redhat linux
kernel 2.4.18-5 ext3fs.
I'm hoping I haven't hit the limit of the hardware or os but here's all
the relevant info. Questions, comments, solutions would be greatly
appreciated.
11696 postgres 25 0 1084M 1.1G 562M R 99.9 28.6 2:36 postmaster
Postgresql.conf settings
shared_buffers = 250000
sort_mem = 1048576 # min 32
vacuum_mem = 128000 # min 1024
wal_files = 64 # range 0-64
enable_seqscan = false
enable_indexscan = true
enable_tidscan = true
enable_sort = true
enable_nestloop = true
enable_mergejoin = true
enable_hashjoin = true
[postgres(at)db1 base]$ cat /proc/sys/kernel/shmmax
2192000000
database=# explain analyze SELECT active,registrant,name FROM person WHERE
object.active = 1 AND object.registrant = 't' ORDER BY UPPER(object.name)
DESC LIMIT 10 OFFSET 0;
NOTICE: QUERY PLAN:
Limit (cost=nan..nan rows=10 width=2017) (actual
time=204790.82..204790.84 rows=10 loops=1)
-> Sort (cost=nan..nan rows=1032953 width=2017) (actual
time=204790.81..204790.82 rows=11 loops=1)
-> Index Scan using registrant__object__idx on object
(cost=0.00..81733.63 rows=1032953 width=2017) (actual
time=0.14..94509.14 rows=1032946 loops=1)
Total runtime: 205125.75 msec
NOTICE: QUERY PLAN:
Limit (cost=nan..nan rows=10 width=2017) (actual
time=204790.82..204790.84 rows=10 loops=1)
-> Sort (cost=nan..nan rows=1032953 width=2017) (actual
time=204790.81..204790.82 rows=11 loops=1)
-> Index Scan using registrant__object__idx on object
(cost=0.00..81733.63 rows=1032953 width=2017) (actual
time=0.14..94509.14 rows=1032946 loops=1)
Total runtime: 205125.75 msec
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2002-12-07 15:48:36 | Re: Speeding up aggregates |
Previous Message | Josh Berkus | 2002-12-07 01:54:43 | Re: Speeding up aggregates |