pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: David Brown <time@bigpond.net.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Seqscan rather than Index
Date: Fri, 17 Dec 2004 13:03:50 +0000
Message-ID: <41C2D936.3060901@archonet.com> (raw)
In-Reply-To: <20041217011853.9E8FF3CADB0@svr1.postgresql.org>
References: <20041217011853.9E8FF3CADB0@svr1.postgresql.org>

David Brown wrote:
>> You might want to reduce random_page_cost a little.
> 
> 
>> Keep in mind that your test case is small enough to fit in RAM and
>> is probably not reflective of what will happen with larger tables.
> 
> 
> I am also running 8.0 rc1 for Windows. Despite many hours spent
> tweaking various planner cost constants, I found little effect on
> cost estimates. Even reducing random_page_cost from 4.0 to 0.1 had
> negligible impact and failed to significantly influence the planner.

I'm not sure setting random_page_cost below 1.0 makes much sense.

> Increasing the statistics target for the last_name column to 250 or
> so *may* help, at least if you're only selecting one name at a time.

Not going to do anything in this case. The planner is roughly right 
about how many rows will be returned, it's just not expecting everything 
to be in RAM.

> That's the standard advice around here and the only thing I've found
> useful. Half the threads in this forum are about under-utilized
> indexes. It would be great if someone could admit the planner is
> broken and talk about actually fixing it!

Not sure I agree here - when the stats are accurate, you can get the 
planner to make near-optimal choices most of the time. Is there any 
particular pattern you've seen?

> I'm unconvinced that the planner only favours sequential scans as
> table size decreases. In my experience so far, larger tables have the
> same problem only it's more noticeable.

Hmm - assuming your statistics are good, this would suggest the other 
cost settings just aren't right for your hardware.

> The issue hits PostgreSQL harder than others because of its awful
> sequential scan speed, which is two to five times slower than other
> DBMS. The archives show there has been talk for years about this, but
> it seems, no solution. The obvious thing to consider is the block
> size, but people have tried increasing this in the past with only
> marginal success.

Must admit this puzzles me. Are you saying you can't saturate your disk 
I/O? Or are you saying other DBMS store records in 0.5 to 0.2 times less 
space than PG?

--
   Richard Huxton
   Archonet Ltd



view thread (16+ messages)  latest in thread

Message-ID: <41C2D936.3060901@archonet.com>
Permalink:  ../41C2D936.3060901@archonet.com/
Also on:    postgresql.org/message-id/41C2D936.3060901@archonet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dev@archonet.com, time@bigpond.net.au
  Subject: Re: Seqscan rather than Index
  In-Reply-To: <41C2D936.3060901@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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