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

Re: TB-sized databases

From: Matthew <matthew(at)flymine(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: TB-sized databases
Date: 2007-11-28 14:40:41
Message-ID: Pine.LNX.4.58.0711281430360.3731@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, 28 Nov 2007, Gregory Stark wrote:
> > Is there something wrong with:
> > set enable_seqscan = off
> > ?
>
> This does kind of the opposite of what you would actually want here. What you
> want is that if you give it a query which would be best satisfied by a
> sequential scan it should throw an error since you've obviously made an error
> in the query.
>
> What this does is it forces such a query to use an even *slower* method such
> as a large index scan. In cases where there isn't any other method it goes
> ahead and does the sequential scan anyways.

The query planner is not always right. I would like an option like
"set enable_seqscan = off" but with the added effect of making Postgres
return an error if there is no alternative to scanning the whole table,
because I have obviously made a mistake setting up my indexes. I would
effectively be telling Postgres "For this table, I *know* that a full
table scan is dumb for all of my queries, even if the statistics say
otherwise."

Of course, it would have to be slightly intelligent, because there are
circumstances where a sequential scan doesn't necessarily mean a full
table scan (for instance if there is a LIMIT), and where an index scan
*does* mean a full table scan (for instance, selecting the whole table and
ordering by an indexed field).

Matthew

-- 
Existence is a convenient concept to designate all of the files that an
executable program can potentially process.   -- Fortran77 standard

In response to

pgsql-performance by date

Next:From: Craig JamesDate: 2007-11-28 14:56:33
Subject: Re: Query only slow on first run
Previous:From: Pablo AlcarazDate: 2007-11-28 14:15:11
Subject: Re: TB-sized databases

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