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

Re: disabling seq scans

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: "Marcus Andree S(dot) Magalhaes" <marcus(dot)magalhaes(at)vlinfo(dot)com(dot)br>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: disabling seq scans
Date: 2004-04-01 21:11:55
Message-ID: Pine.LNX.4.33.0404011401200.30598-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
On Thu, 1 Apr 2004, Marcus Andree S. Magalhaes wrote:

> 
> Hi, guys,
> 
> We're experiencing a little problem with one of our queries.
> It isn't using an index specially created for it. When we
> disable seq scans with psql, we can ensure the query finishes
> much faster than without using index, as it should be.
> 
> So, whats the best procedure in this case, but when have a
> JDBC based client? Do we mess around with planner
> settings even when all other queries are using the best
> index for them?
> 
> Is it safe (but some may find ugly) to issue a command to
> disable seq scanning from the java side?
> 
> Since we're using the pooled connection classes that comes
> with the JDBC3 driver, once a connection is got from the pool,
> do we need to explicitly set seq scanning to true? This is
> assuming the later option is the more recommended one...

What's likely happening is that the queries that are choosing the right 
plan (i.e. the other queries) are such obviously bad candidates for a seq 
scan that they still get an index scan.  It might be that as you increase 
the % of the table being read by those other queries that they might 
switch to a seq scan too soon for your setup.

I'm assuming you've read the tuning guide here:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

And upped shared buffers, effect_cache_size, etc...

random page costs of 1.0 to 1.4 make sense for certain setups with lots of 
RAM and medium to small datasets that can (mostly) fit in memory.  On our 
machine with 2G ram at work, that works out to about 1.2 to 1.3 as 
optimal.


In response to

Responses

pgsql-jdbc by date

Next:From: Marcus Andree S. MagalhaesDate: 2004-04-01 21:12:08
Subject: Re: disabling seq scans
Previous:From: Sean ShannyDate: 2004-04-01 20:36:24
Subject: Re: disabling seq scans

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