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@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