pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dmitry Karasik <dmitry@karasik.eu.org>
To: pgsql-performance@postgresql.org
Subject: VACUUM ANALYZE downgrades performance
Date: 30 Nov 2004 14:30:37 +0100
Message-ID: <84fz2ra2gy.fsf_-_@plab.ku.dk> (raw)


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)

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'.

-- 
Sincerely,
	Dmitry Karasik

---
catpipe Systems ApS
*BSD solutions, consulting, development
www.catpipe.net
+45 7021 0050  




view thread (8+ messages)  latest in thread

Message-ID: <84fz2ra2gy.fsf_-_@plab.ku.dk>
Permalink:  ../84fz2ra2gy.fsf_-_@plab.ku.dk/
Also on:    postgresql.org/message-id/84fz2ra2gy.fsf_-_@plab.ku.dk

 · 

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: dmitry@karasik.eu.org
  Subject: Re: VACUUM ANALYZE downgrades performance
  In-Reply-To: <84fz2ra2gy.fsf_-_@plab.ku.dk>

* 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