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

* R: R: slow seqscan after vacuum analize
@ 2004-02-05 01:06 ` Edoardo Ceccarelli <eddy@axa.it>
  1 sibling, 0 replies; 9+ messages in thread

From: Edoardo Ceccarelli @ 2004-02-05 01:06 UTC (permalink / raw)
  To: 'Sam Barnett-Cormack' <s.barnett-cormack@lancaster.ac.uk>; +Cc: pgsql-admin

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

Do you know how can I check if the storage method has changed? 
I was thinking that the priority target of a vacuum operation is to reclaim disk space
- this might imply that the performance are worst for a seqscan - maybe it's normal.
Anyway, I am doing a VACUUM FULL ANALYZE right now to see if things get better.

Thanks for you hints
Edoardo




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

* R: R: slow seqscan after vacuum analize
@ 2004-02-05 02:10 ` Edoardo Ceccarelli <eddy@expot.it>
  1 sibling, 0 replies; 9+ messages in thread

From: Edoardo Ceccarelli @ 2004-02-05 02:10 UTC (permalink / raw)
  To: 'Sam Barnett-Cormack' <s.barnett-cormack@lancaster.ac.uk>; +Cc: 'scott.marlowe' <scott.marlowe@ihs.com>; pgsql-admin

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

After a VACUUM FULL ANALYZE:

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

--------------------------------------------------------------------------------------
--------------------
 Seq Scan on utente  (cost=0.00..41329.21 rows=154 width=724) (actual
time=91.61..751.28 rows=15 loops=1)
   Filter: (luogorilasciodoc = 'ciao'::bpchar)
 Total runtime: 751.35 msec


Things are better now! :) it surely was because i have used the normal vacuum...
 





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

* R: R: slow seqscan after vacuum analize
@ 2004-02-05 02:18 Edoardo Ceccarelli <eddy@axa.it>
  2004-02-05 04:18 ` Re: R: R: slow seqscan after vacuum analize Christopher Browne <cbbrowne@acm.org>
  2004-02-05 15:00 ` Re: R: R: slow seqscan after vacuum analize Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
  0 siblings, 2 replies; 9+ messages in thread

From: Edoardo Ceccarelli @ 2004-02-05 02:18 UTC (permalink / raw)
  To: 'David F. Skoll' <dfs@roaringpenguin.com>; '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 

Best Regards
Edoardo

> -----Messaggio originale-----
> Da: David F. Skoll [mailto:dfs@roaringpenguin.com] 
> Inviato: giovedì 5 febbraio 2004 3.17
> A: Edoardo Ceccarelli
> Cc: pgsql-admin@postgresql.org
> Oggetto: Re: R: [ADMIN] slow seqscan after vacuum analize
> 
> 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] 9+ messages in thread

* Re: R: R: slow seqscan after vacuum analize
  2004-02-05 02:18 R: R: slow seqscan after vacuum analize Edoardo Ceccarelli <eddy@axa.it>
@ 2004-02-05 04:18 ` Christopher Browne <cbbrowne@acm.org>
  2004-02-05 17:09   ` Re: R: R: slow seqscan after vacuum analize Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 9+ messages in thread

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

In the last exciting episode, eddy@axa.it ("Edoardo Ceccarelli") 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 sounds as though you weren't vacuuming ("just plain vacuum") often
enough.

What tends to go wrong is when the table winds up with a LOT of empty
space due to there being a lot of updates to the table without dead
tuples being cleaned out.  The table winds up big, with no way to
shrink it without the cost of a VACUUM FULL.

If you vacuumed more often, the size of the table would likely stay
smaller which is sure to be helpful.

Another factor worth considering: If a few values are very common in
the field you are selecting on, then the query optimizer can get
convinced (wrongly) that a Seq Scan is the best choice.  Using ALTER
TABLE T ALTER COLUMN C SET STATISTICS [some value] to increase the
number of "bins" can be helpful in such cases.  (My pet theory is that
the present default value of 10 is a little low, and that a lot of
optimizer errors might be resolved by bumping it up a bit...)
-- 
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://www.ntlug.org/~cbbrowne/sgml.html
But  what can  you  do with  it?   -- ubiquitous  cry from  Linux-user
partner.  -- Andy Pearce, <ajp@hpopd.pwd.hp.com>



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

* Re: R: R: slow seqscan after vacuum analize
  2004-02-05 02:18 R: R: slow seqscan after vacuum analize Edoardo Ceccarelli <eddy@axa.it>
  2004-02-05 04:18 ` Re: R: R: slow seqscan after vacuum analize Christopher Browne <cbbrowne@acm.org>
@ 2004-02-05 17:09   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2004-02-05 17:09 UTC (permalink / raw)
  To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-admin

Christopher Browne <cbbrowne@acm.org> writes:
> Another factor worth considering: If a few values are very common in
> the field you are selecting on, then the query optimizer can get
> convinced (wrongly) that a Seq Scan is the best choice.  Using ALTER
> TABLE T ALTER COLUMN C SET STATISTICS [some value] to increase the
> number of "bins" can be helpful in such cases.  (My pet theory is that
> the present default value of 10 is a little low, and that a lot of
> optimizer errors might be resolved by bumping it up a bit...)

I also suspect that 10 is a lowball figure, but no one has done any work
to establish what might be a more reasonable default.  Larger values
have real costs in both pg_statistic space and planner runtime, so I
don't want to push it up without some kind of evidence.

BTW, if you think a higher default might be appropriate, you can set it
in postgresql.conf instead of running around and manually ALTERing all
your tables.

			regards, tom lane



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

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

From: Sam Barnett-Cormack @ 2004-02-05 15:00 UTC (permalink / raw)
  To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: 'David F. Skoll' <dfs@roaringpenguin.com>; pgsql-admin

On Thu, 5 Feb 2004, Edoardo Ceccarelli wrote:

> after a VACUUM FULL ANALYZE (1h!!) things are ok

Only a hour - lucky you ;)

-- 

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



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

* R: R: slow seqscan after vacuum analize
@ 2004-02-05 17:00 Edoardo Ceccarelli <eddy@axa.it>
  2004-02-05 19:33 ` Re: R: R: slow seqscan after vacuum analize Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Edoardo Ceccarelli @ 2004-02-05 17:00 UTC (permalink / raw)
  To: 'Christopher Browne' <cbbrowne@acm.org>; pgsql-admin

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

I have only noticed that after a VACUUM ANALYZE of the db the time of a seq scan (when
a seqscan is required) has increased by a factor of ten, this is my primary focus,
otherwise I would have posted something about "my select is really slow ..."

To be more precise: 
Given a query that HAS to be executed with a seqscan I have noticed an increase in
time comparing before and after the vacuum.


Anyway I am working to create the same situation again to post some output of the
verbose option of the vacuum.

Edoardo




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

* Re: R: R: slow seqscan after vacuum analize
  2004-02-05 17:00 R: R: slow seqscan after vacuum analize Edoardo Ceccarelli <eddy@axa.it>
@ 2004-02-05 19:33 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-02-06 00:12   ` Re: R: R: slow seqscan after vacuum analize Edoardo <eddy@axa.it>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2004-02-05 19:33 UTC (permalink / raw)
  To: eddy@axa.it; +Cc: 'Christopher Browne' <cbbrowne@acm.org>; pgsql-admin

"Edoardo Ceccarelli" <eddy@axa.it> writes:
> Given a query that HAS to be executed with a seqscan I have noticed an
> increase in time comparing before and after the vacuum.

This is really hard to believe --- I cannot think of any mechanism that
would result in that effect.  Unless the vacuum were flushing the
kernel's disk cache, but the effects of that would only persist for one
scan.  You did say that the increased time is repeatable if you do
multiple seqscans after the vacuum?

			regards, tom lane



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

* Re: R: R: slow seqscan after vacuum analize
  2004-02-05 17:00 R: R: slow seqscan after vacuum analize Edoardo Ceccarelli <eddy@axa.it>
  2004-02-05 19:33 ` Re: R: R: slow seqscan after vacuum analize Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-06 00:12   ` Edoardo <eddy@axa.it>
  0 siblings, 0 replies; 9+ messages in thread

From: Edoardo @ 2004-02-06 00:12 UTC (permalink / raw)
  To: pgsql-admin

On Thu, 05 Feb 2004 14:33:45 -0500, Tom Lane wrote
> "Edoardo Ceccarelli" <eddy@axa.it> writes:
> > Given a query that HAS to be executed with a seqscan I have noticed an
> > increase in time comparing before and after the vacuum.
> 
> This is really hard to believe --- I cannot think of any mechanism that
> would result in that effect.  Unless the vacuum were flushing the
> kernel's disk cache, but the effects of that would only persist for one
> scan.  You did say that the increased time is repeatable if you do
> multiple seqscans after the vacuum?
> 
> 			regards, tom lane


Yes, I can assure you that was repeatable and has disappeared only after a 
VACUUM FULL ANALYZE 

it was something really stable in it's bad behaviour. I am going to make some 
test soon and I will post here the results.

Best Regards
Edoardo

--
The net spotter (http://expot.it)
Open WebMail Project (http://openwebmail.org)




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


end of thread, other threads:[~2004-02-06 00:12 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-05 01:06 ` Edoardo Ceccarelli <eddy@axa.it>
2004-02-05 02:10 ` Edoardo Ceccarelli <eddy@expot.it>
2004-02-05 02:18 R: R: slow seqscan after vacuum analize Edoardo Ceccarelli <eddy@axa.it>
2004-02-05 04:18 ` Christopher Browne <cbbrowne@acm.org>
2004-02-05 17:09   ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-05 15:00 ` Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
2004-02-05 17:00 R: R: slow seqscan after vacuum analize Edoardo Ceccarelli <eddy@axa.it>
2004-02-05 19:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-06 00:12   ` Edoardo <eddy@axa.it>

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