Skip site navigation (1) Skip section navigation (2)

Query optimization

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 (view raw or flat)
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



Responses

pgsql-performance by date

Next:From: Ron JohnsonDate: 2002-12-07 15:48:36
Subject: Re: Speeding up aggregates
Previous:From: Josh BerkusDate: 2002-12-07 01:54:43
Subject: Re: Speeding up aggregates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group