pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mike Rylander <mrylander@gmail.com>
To: Dmitry Karasik <dmitry@karasik.eu.org>
To: pgsql-performance@postgresql.org
Subject: Re: VACUUM ANALYZE downgrades performance
Date: Tue, 30 Nov 2004 10:33:01 -0500
Message-ID: <b918cf3d04113007333f5ff652@mail.gmail.com> (raw)
In-Reply-To: <84fz2ra2gy.fsf_-_@plab.ku.dk>
References: <84fz2ra2gy.fsf_-_@plab.ku.dk>

On 30 Nov 2004 14:30:37 +0100, Dmitry Karasik <dmitry@karasik.eu.org> wrote:
> 
> Hi all,
> 
> On v7.4.5 I noticed downgrade in the planner, namely favoring
> sequential scan over index scan. The proof:
> 
>    create table a ( a integer);
>    create index aidx on a(a);
>    explain analyze select * from a where a = 0;
>    -- Index Scan using aidx on a  (cost=0.00..17.07 rows=5 width=4) (actual
>    --   time=0.029..0.029 rows=0 loops=1)
>    -- Index Cond: (a = 0)
>    vacuum analyze;
>    explain analyze select * from a where a = 0;
>    -- Seq Scan on a (cost=0.00..0.00 rows=1 width=4) (actual time=0.009..0.009
>    --   rows=0 loops=1)
>    -- Filter: (a = 0)

Looks to me like the seq scan is a better plan.  The "actual time" went down.

> 
> I do realize that there might be reasons why this happens over an empty
> table, but what is way worse that when the table starts actually to fill,
> the seq scan is still there, and the index is simply not used. How
> that could be so ...mmm... shortsighted, and what is more important,
> how to avoid this? I hope the answer is not 'run vacuum analyze each 5 seconds'.
> 

See this thread
(http://archives.postgresql.org/pgsql-hackers/2004-11/msg00985.php and
http://archives.postgresql.org/pgsql-hackers/2004-11/msg01080.php) for
an ongoing discussion of the issue.


-- 
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org



view thread (8+ messages)  latest in thread

Message-ID: <b918cf3d04113007333f5ff652@mail.gmail.com>
Permalink:  ../b918cf3d04113007333f5ff652@mail.gmail.com/
Also on:    postgresql.org/message-id/b918cf3d04113007333f5ff652@mail.gmail.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: mrylander@gmail.com, dmitry@karasik.eu.org
  Subject: Re: VACUUM ANALYZE downgrades performance
  In-Reply-To: <b918cf3d04113007333f5ff652@mail.gmail.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