Re: Query optimization

Fred Moyer wrote:
>
> 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.

> Postgresql.conf settings
> shared_buffers = 250000

This looks awfull high to me. 25000 might be better to give more room to
the OS disk-caching. Bit of a waste if PostgreSQL and the OS start
caching exactly the same blocks.
Trying is the only way to find a good setting.


> sort_mem = 1048576            # min 32
> vacuum_mem = 128000          # min 1024
> wal_files = 64 # range 0-64
> enable_seqscan = false

Why disable seqscan? For any query that is not particularly selective
this will mean a performance hit.


> enable_indexscan = true
> enable_tidscan = true
> enable_sort = true
> enable_nestloop = true
> enable_mergejoin = true
> enable_hashjoin = true

> 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

I think this is an example of a not particularly selective query. If I
read it correctly, pretty much every row satisfies the predicates
object.active = 1 AND object.registrant = 't' (how much do not satisfy
these predicates?).

Jochem