pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Enabling and Disabling Sequencial Scan
6+ messages / 4 participants
[nested] [flat]

* Enabling and Disabling Sequencial Scan
@ 2003-05-30 20:33  Yusuf <yusuf0478@netscape.net>
  0 siblings, 1 reply; 6+ messages in thread

From: Yusuf @ 2003-05-30 20:33 UTC (permalink / raw)
  To: pgsql-performance

In the application, that I'm working on, I have a query that'll be a lot 
60% faster if I disable sequential scan forcing it to you my index.

Is it bad practice to disable sequential scan ( set 
enable_seqscan=false), run my query then enable sequential scan, 
whenever I'm running this query?  Why?

Thanks in advance

- David Wendy




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Enabling and Disabling Sequencial Scan
@ 2003-05-30 20:46  scott.marlowe <scott.marlowe@ihs.com>
  parent: Yusuf <yusuf0478@netscape.net>
  0 siblings, 1 reply; 6+ messages in thread

From: scott.marlowe @ 2003-05-30 20:46 UTC (permalink / raw)
  To: Yusuf <yusuf0478@netscape.net>; +Cc: pgsql-performance

On Fri, 30 May 2003, Yusuf wrote:

> In the application, that I'm working on, I have a query that'll be a lot 
> 60% faster if I disable sequential scan forcing it to you my index.
> 
> Is it bad practice to disable sequential scan ( set 
> enable_seqscan=false), run my query then enable sequential scan, 
> whenever I'm running this query?  Why?

setting seqscan to off is more of a troubleshooting tool than a tuning 
tool, albeit sometimes it's the only tuning tool that MIGHT work.

Once you've determined that the database is picking the wrong plan when 
you turn seqscan back on, you need to figure out how to convince the 
database to use the right plan more often.

The best parameters to change and see how they affect this are the 
*cost* parameters and the effective cache size.

show all; will show them to you, the ones we're interested in are these:

NOTICE:  effective_cache_size is 100000
NOTICE:  random_page_cost is 1
NOTICE:  cpu_tuple_cost is 0.01
NOTICE:  cpu_index_tuple_cost is 0.0001
NOTICE:  cpu_operator_cost is 0.0025

To change them for one session, just use the set command.  To make the 
changes the permanent default, edit the $PGDATA/postgresql.conf file.

effective_cache_size tells the planner about how big the kernel's file 
level cache is.  On my machine it's about 800 meg. It's measured in 8k 
blocks, so 100,000 * 8k ~ 800 meg.  The smaller this is, the more likely 
the database will have to access the hard drive, and therefore the more 
likely it will pick a seqscan if the other numbers point to it.

random_page_cost tells the planner how much more a random page access 
costs.  The default is 4.  Most systems seem to work well with numbers 
from 1 to 2.

lowering the cpu_index_tuple_cost also favors index scans.





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Enabling and Disabling Sequencial Scan
@ 2003-05-31 03:28  Robert Creager <Robert_Creager@LogicalChaos.org>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Robert Creager @ 2003-05-31 03:28 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: pgsql-performance

On Fri, 30 May 2003 14:46:12 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com> said something like:

> 
> level cache is.  On my machine it's about 800 meg. It's measured in 8k > blocks, so 100,000 * 8k ~ 800 meg.  The smaller this is, the more 

Any thoughts on how to figure this out (disk buffer size)?  For some reason, my system (2xAMD 2800+, 2Gb RAM 2.4.21 - /proc/meminfo) only shows a usage of  88kb of 'Buffers' usage, and that never changes.  My 'Cached' usage is 1.7Gb.  I've hit the kernel mailing list, and the one response I got said don't worry about it :-(

Cheers,
Rob

-- 
O_

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Enabling and Disabling Sequencial Scan
@ 2003-05-31 15:07  Nikolaus Dilger <nikolaus@dilger.cc>
  0 siblings, 0 replies; 6+ messages in thread

From: Nikolaus Dilger @ 2003-05-31 15:07 UTC (permalink / raw)
  To: yusuf0478@netscape.net; +Cc: pgsql-performance

David,

I say go ahead and use it since you get a significant
performance gain.  This is a special case where you
know more about your data than the planer does with
general system wide settings.  In Oracle you could use
"hints".  Since there are no hints in PostgreSQL
disabling and reenabling an option just before and
after a query has the same effect.

Regards,
Nikolaus

On Fri, 30 May 2003 16:33:07 -0400, Yusuf wrote:

> 
> In the application, that I'm working on, I have a
query
> that'll be a lot 
> 60% faster if I disable sequential scan forcing it to
> you my index.
> 
> Is it bad practice to disable sequential scan ( set 
> enable_seqscan=false), run my query then enable
> sequential scan, 
> whenever I'm running this query?  Why?
> 
> Thanks in advance
> 
> - David Wendy
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Enabling and Disabling Sequencial Scan
@ 2003-06-02 15:20  scott.marlowe <scott.marlowe@ihs.com>
  parent: Robert Creager <Robert_Creager@LogicalChaos.org>
  1 sibling, 0 replies; 6+ messages in thread

From: scott.marlowe @ 2003-06-02 15:20 UTC (permalink / raw)
  To: Robert Creager <Robert_Creager@LogicalChaos.org>; +Cc: pgsql-performance

On Fri, 30 May 2003, Robert Creager wrote:

> On Fri, 30 May 2003 14:46:12 -0600 (MDT)
> "scott.marlowe" <scott.marlowe@ihs.com> said something like:
> 
> > 
> > level cache is.  On my machine it's about 800 meg. It's measured in 8k > blocks, so 100,000 * 8k ~ 800 meg.  The smaller this is, the more 
> 
> Any thoughts on how to figure this out (disk buffer size)?  For some 
> reason, my system (2xAMD 2800+, 2Gb RAM 2.4.21 - /proc/meminfo) only 
> shows a usage of  88kb of 'Buffers' usage, and that never changes.  My 
> 'Cached' usage is 1.7Gb.  I've hit the kernel mailing list, and the one 
> response I got said don't worry about it :-(

Are you sure that's not 88213Kb or so of buffers?  88kb is awfully small.

It's normal to have a cache size many times larger than the buffer size.  
Buffers are assigned to individual disks, and sit under the larger single 
pool that is the cache.

I just take the approximate size of the cache under load and use that for 
the effective_cache_size.  Since it's pretty much a "fudge factor" 
variable anyway.

P.s. My use of the term fudge factor here is in no way meant to be 
derogatory.  It's just that as long as the effective cache size is within 
some reasonable range of the actual cache/buffer in the machine, it'll be 
close enough to push the query planner in the right direction.

Note that you can adopt two philosophies on the planner.  One is that the 
planner will always make certain mistakes, and you've got to fool it in 
order to get the right query plan.

The other philosophy is that you give the query planner all the variables 
you can reasonably give it to let it decide the proper course of action, 
and you fine tune each one so that eventually it makes the right choice 
for all the querys you throw at it.

While the first philosophy provides for the fastest functional solutions 
on a static platform (i.e. we're running postgresql 7.0.2 and aren't going 
to upgrade.) but it kind of supports the idea that the query planner can 
never be given the right information and programmed with the right code to 
make the right decision 99% of the time, and when it makes the wrong 
decision, it's only a little wrong.

The second choice will require you to spend more time fine tuning all the 
parameters fed to the query planner with your own queries using explain 
analyze and repeated testing with different settings.

What I look for are the corner cases.  I.e. if I do some select that 
returns 500 records with a seq scan, and it takes 5 seconds, and with 450 
records it switches to index scan and takes 1 second, then likely the 
planner is choosing to switch to seq scans too quickly when I raise the 
result size from 450 to 500.  

At this point use the set seq_scan option to test the database 
performance with it on and off and increasing set size.

Somewhere around 2,000 or so in this scenario, we'll notice that the seq 
scan has now the same speed as the index scan, and as we raise the number 
of rows we are getting, the index scan would now be slower than the seq 
scan.

Assuming we set effective_cache_size right at the beginning, we now can   
turn seq_scan back on, and adjust the default cost options until the 
planner chooses a seq scan at the break point we found (in our imaginary 
case of 2000).  It doesn't have to be perfect, since the performance at or 
around the break point is similar for index and seq scans alike.

Then, throw the next query at it and see how it does.

I've found that on fast machines, it's good to lower the cpu costs, 
especially the index one.  I usually drop these by a divisor of 2 to 10.  
For the random_page_cost, settings of 1.x to 2.x seem a good choice for 
fast I/O subsystems.




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Enabling and Disabling Sequencial Scan
@ 2003-06-02 15:23  scott.marlowe <scott.marlowe@ihs.com>
  parent: Robert Creager <Robert_Creager@LogicalChaos.org>
  1 sibling, 0 replies; 6+ messages in thread

From: scott.marlowe @ 2003-06-02 15:23 UTC (permalink / raw)
  To: Robert Creager <Robert_Creager@LogicalChaos.org>; +Cc: pgsql-performance

On Fri, 30 May 2003, Robert Creager wrote:

> On Fri, 30 May 2003 14:46:12 -0600 (MDT)
> "scott.marlowe" <scott.marlowe@ihs.com> said something like:
> 
> > 
> > level cache is.  On my machine it's about 800 meg. It's measured in 8k 
> > blocks, so 100,000 * 8k ~ 800 meg.  The smaller this is, the more 
> 
> My 'Cached' usage is 1.7Gb.  I've hit the kernel mailing list, and the 
> one response I got said don't worry about it :-(

Oh, yeah, just a bit on that.  as far as the kernel developers are 
concerned, the buffer / cache is working perfectly, and they're right, it 
is.  What they probably don't understand if your need to tell postgresql 
how much cache/buffer is allocated to it.  

so don't worry about the kernel, the linux kernel really is pretty good at 
caching disk access.




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-06-02 15:23 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-30 20:33 Enabling and Disabling Sequencial Scan Yusuf <yusuf0478@netscape.net>
2003-05-30 20:46 ` scott.marlowe <scott.marlowe@ihs.com>
2003-05-31 03:28   ` Robert Creager <Robert_Creager@LogicalChaos.org>
2003-06-02 15:20     ` scott.marlowe <scott.marlowe@ihs.com>
2003-06-02 15:23     ` scott.marlowe <scott.marlowe@ihs.com>
2003-05-31 15:07 Re: Enabling and Disabling Sequencial Scan Nikolaus Dilger <nikolaus@dilger.cc>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox