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

Re: Query optimization

From: Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)nl>
To: fred(at)digicamp(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query optimization
Date: 2002-12-07 20:41:57
Message-ID: 3DF25D15.6060006@oli.tudelft.nl (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: Richard HuxtonDate: 2002-12-08 13:48:44
Subject: Re: Query optimization
Previous:From: Fred MoyerDate: 2002-12-07 20:10:41
Subject: Re: Query optimization

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