pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
R: slow seqscan after vacuum analize
6+ messages / 6 participants
[nested] [flat]

* R: slow seqscan after vacuum analize
@ 2004-02-05 00:35  Edoardo Ceccarelli <eddy@axa.it>
  0 siblings, 2 replies; 6+ messages in thread

From: Edoardo Ceccarelli @ 2004-02-05 00:35 UTC (permalink / raw)
  To: 'scott.marlowe' <scott.marlowe@ihs.com>; +Cc: pgsql-admin

> -----Messaggio originale-----
> Da: scott.marlowe [mailto:scott.marlowe@ihs.com] 
> Inviato: mercoledì 4 febbraio 2004 22.45
> A: Edoardo Ceccarelli
> Cc: pgsql-admin@postgresql.org
> Oggetto: Re: [ADMIN] slow seqscan after vacuum analize
> 
> On Wed, 4 Feb 2004, Edoardo Ceccarelli wrote:
> 
> > I have a simple query that scans each record, like this: 
> select * from 
> > utente where luogorilasciodoc='ciao'
> > The execution time BEFORE vacuum is: 1203ms The execution 
> time AFTER 
> > vacuum is: 6656ms !!!
> > What is going on? Thought that one vaccum the db to get 
> better performance!
> > 
> > PostgreSQL Ver. 7.3.4 on linux
> 
> Can you show us the output of "explain analyze select * from 
> ..." for both 
> before and after a vacuum?   i.e. with the difference in 
> performance.  If 
> not, just an explain analyze of the way it runs now might be enough.
> 


BEFORE (copy of the db without having run the vacuum):

explain analyze SELECT * FROM utente where luogorilasciodoc='ciao';
                                                 QUERY PLAN

--------------------------------------------------------------------------------------
----------------------
 Seq Scan on utente  (cost=0.00..40947.90 rows=1826 width=724) (actual
time=131.17..856.96 rows=15 loops=1)
   Filter: (luogorilasciodoc = 'ciao'::bpchar)
 Total runtime: 857.06 msec
(3 rows)


AFTER (actual db)
portaportese=# explain analyze SELECT * FROM utente where luogorilasciodoc='ciao';
                                                QUERY PLAN

--------------------------------------------------------------------------------------
--------------------
 Seq Scan on utente  (cost=0.00..92174.50 rows=3 width=724) (actual
time=705.41..6458.19 rows=15 loops=1)
   Filter: (luogorilasciodoc = 'ciao'::bpchar)
 Total runtime: 6458.29 msec
(3 rows

Things are worst only for seqscan, when it uses indexscan timing is good.





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

* Re: R: slow seqscan after vacuum analize
@ 2004-02-05 00:56  Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
  parent: Edoardo Ceccarelli <eddy@axa.it>
  1 sibling, 0 replies; 6+ messages in thread

From: Sam Barnett-Cormack @ 2004-02-05 00:56 UTC (permalink / raw)
  To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: 'scott.marlowe' <scott.marlowe@ihs.com>; pgsql-admin

On Thu, 5 Feb 2004, Edoardo Ceccarelli wrote:

> BEFORE (copy of the db without having run the vacuum):
>
> explain analyze SELECT * FROM utente where luogorilasciodoc='ciao';
>                                                  QUERY PLAN
>
> --------------------------------------------------------------------------------------
> ----------------------
>  Seq Scan on utente  (cost=0.00..40947.90 rows=1826 width=724) (actual
> time=131.17..856.96 rows=15 loops=1)
>    Filter: (luogorilasciodoc = 'ciao'::bpchar)
>  Total runtime: 857.06 msec
> (3 rows)
>
>
> AFTER (actual db)
> portaportese=# explain analyze SELECT * FROM utente where luogorilasciodoc='ciao';
>                                                 QUERY PLAN
>
> --------------------------------------------------------------------------------------
> --------------------
>  Seq Scan on utente  (cost=0.00..92174.50 rows=3 width=724) (actual
> time=705.41..6458.19 rows=15 loops=1)
>    Filter: (luogorilasciodoc = 'ciao'::bpchar)
>  Total runtime: 6458.29 msec
> (3 rows
>
> Things are worst only for seqscan, when it uses indexscan timing is good.

Only thing I can think of is if storage method had been changed. Not
sure if that would even affect it, or if it could do that by itself.
Just brainstorming.

-- 

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University



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

* Re: R: slow seqscan after vacuum analize
@ 2004-02-05 02:17  David F. Skoll <dfs@roaringpenguin.com>
  parent: Edoardo Ceccarelli <eddy@axa.it>
  1 sibling, 0 replies; 6+ messages in thread

From: David F. Skoll @ 2004-02-05 02:17 UTC (permalink / raw)
  To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-admin

On Thu, 5 Feb 2004, Edoardo Ceccarelli wrote:

> Things are worst only for seqscan, when it uses indexscan timing is good.

It might just be that running VACUUM flushed the operating system's
buffer cache.

I always try running two or three EXPLAIN ANALYZE's in a row for the
same query.  Usually, the first one takes much longer than the rest.

Regards,

David.



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

* Re: R: slow seqscan after vacuum analize
@ 2004-02-05 06:32  Iain <iain@mst.co.jp>
  0 siblings, 2 replies; 6+ messages in thread

From: Iain @ 2004-02-05 06:32 UTC (permalink / raw)
  To: 'Edoardo Ceccarelli' <eddy@axa.it>; +Cc: pgsql-admin



> Yes, you are right but it wasn't the case this time, I have run the
explain plenty of
> times with same results. I think that the reason was that I made a simple
VACUUM,
> after a VACUUM FULL ANALYZE (1h!!) things are ok

It's reasonable to expect that a seq scan will perform faster after a full
vacuum, as the physical size and organization of the table has been changed.

I wouldn't expact a plain vacuum to have any tangible affect on performance,
for the better or for the worse..

I'd like to know more about the possibility of plain vacuums harming
performance. This is the first I've heard of it. Vacuum full is not always
an option in a production environment.




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

* Re: R: slow seqscan after vacuum analize
@ 2004-02-05 13:15  Christopher Browne <cbbrowne@acm.org>
  parent: Iain <iain@mst.co.jp>
  1 sibling, 0 replies; 6+ messages in thread

From: Christopher Browne @ 2004-02-05 13:15 UTC (permalink / raw)
  To: pgsql-admin

Centuries ago, Nostradamus foresaw when iain@mst.co.jp ("Iain") would write:
> I'd like to know more about the possibility of plain vacuums harming
> performance. This is the first I've heard of it. Vacuum full is not always
> an option in a production environment.

There certainly are known cases where systems where the I/O bus is
already fairly much saturated will suffer BADLY when a big vacuum is
thrown at them.

The problem in such cases is that the vacuum draws the pages that it
is working on into the buffer cache, pushing out data that is actually
useful to cache.

There are experimental patches for 7.4, 7.3, and even, I believe, 7.2,
for a "sleeping vacuum" that tries to limit the damage by sleeping
every so often so that the vacuum does not dominate, and so that
"ordinary traffic" gets a chance to reclaim cache.  And there are
efforts in 7.5 to improve cache management, so that pages brought in
by VACUUM would be put at the opposite end of the "LRU" queue.  That
way, instead of them being treated as Most Recently Used, pushing
everything the least bit older towards being dropped from the buffer
cache, the vacuumed pages would be treated as if they were LRU, so
they would get evicted FIRST.

But if the Original Poster is encountering that the database is doing
Seq Scans when it would be better to do an Index Scan, that is a
separate problem, and focusing on the VACUUM may distract from the
_real_ problem...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/advocacy.html
Rules of the Evil Overlord #195. "I will not use hostages as bait in a
trap.  Unless  you're going  to use them  for negotiation or  as human
shields, there's no point in taking them."
<http://www.eviloverlord.com/;



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

* Re: R: slow seqscan after vacuum analize
@ 2004-02-05 16:23  Andrew Biagioni <andrew.biagioni@e-greek.net>
  parent: Iain <iain@mst.co.jp>
  1 sibling, 0 replies; 6+ messages in thread

From: Andrew Biagioni @ 2004-02-05 16:23 UTC (permalink / raw)
  To: Iain <iain@mst.co.jp>; +Cc: Edoardo Ceccarelli <eddy@axa.it>; pgsql-admin

Iain wrote:

>  
>
>>Yes, you are right but it wasn't the case this time, I have run the
>>    
>>
>explain plenty of
>  
>
>>times with same results. I think that the reason was that I made a simple
>>    
>>
>VACUUM,
>  
>
>>after a VACUUM FULL ANALYZE (1h!!) things are ok
>>    
>>
>
>It's reasonable to expect that a seq scan will perform faster after a full
>vacuum, as the physical size and organization of the table has been changed.
>
>I wouldn't expact a plain vacuum to have any tangible affect on performance,
>for the better or for the worse..
>  
>
A plain VACUUM (without ANALYZE) will change the layout of the data 
without refreshing the optimizer information, so that anything that DOES 
use the optimizer will often be negatively affected;  VACUUM ANALYZE 
does a vacuum AND refreshes the optimizer information. 

I usually run VACUUM ANALYZE on an hourly basis on our production 
system, and it's fast enough and unobtrusive enough that I can't tell 
that it's running (except by looking at the log, of course).

>I'd like to know more about the possibility of plain vacuums harming
>performance. This is the first I've heard of it. Vacuum full is not always
>an option in a production environment.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>

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


end of thread, other threads:[~2004-02-05 16:23 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-05 00:35 R: slow seqscan after vacuum analize Edoardo Ceccarelli <eddy@axa.it>
2004-02-05 00:56 ` Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
2004-02-05 02:17 ` David F. Skoll <dfs@roaringpenguin.com>
2004-02-05 06:32 Re: R: slow seqscan after vacuum analize Iain <iain@mst.co.jp>
2004-02-05 13:15 ` Christopher Browne <cbbrowne@acm.org>
2004-02-05 16:23 ` Andrew Biagioni <andrew.biagioni@e-greek.net>

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