pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
VACUUM ANALYZE downgrades performance
8+ messages / 6 participants
[nested] [flat]

* VACUUM ANALYZE downgrades performance
@ 2004-11-30 13:30  Dmitry Karasik <dmitry@karasik.eu.org>
  0 siblings, 2 replies; 8+ messages in thread

From: Dmitry Karasik @ 2004-11-30 13:30 UTC (permalink / raw)
  To: pgsql-performance


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  




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

* Re: VACUUM ANALYZE downgrades performance
@ 2004-11-30 15:33  Mike Rylander <mrylander@gmail.com>
  parent: Dmitry Karasik <dmitry@karasik.eu.org>
  1 sibling, 0 replies; 8+ messages in thread

From: Mike Rylander @ 2004-11-30 15:33 UTC (permalink / raw)
  To: Dmitry Karasik <dmitry@karasik.eu.org>; pgsql-performance

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



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

* Re: VACUUM ANALYZE downgrades performance
@ 2004-11-30 15:42  Thomas Swan <tswan@idigx.com>
  parent: Dmitry Karasik <dmitry@karasik.eu.org>
  1 sibling, 1 reply; 8+ messages in thread

From: Thomas Swan @ 2004-11-30 15:42 UTC (permalink / raw)
  To: Dmitry Karasik <dmitry@karasik.eu.org>; +Cc: pgsql-performance

On 11/30/2004 7:30 AM Dmitry Karasik said::

>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'.
>
>  
>
Look at the ACTUAL TIME.   It dropped from 0.029ms (using the index 
scan) to 0.009ms (using a sequential scan.)  

Index scans are not always faster, and the planner/optimizer knows 
this.  VACUUM ANALYZE is best run when a large proportion of data has 
been updated/loaded or in the off hours to refresh the statistics on 
large datasets.







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

* Re: VACUUM ANALYZE downgrades performance
@ 2004-12-02 16:07  Dmitry Karasik <dmitry@karasik.eu.org>
  parent: Thomas Swan <tswan@idigx.com>
  0 siblings, 3 replies; 8+ messages in thread

From: Dmitry Karasik @ 2004-12-02 16:07 UTC (permalink / raw)
  To: pgsql-performance

	Hi Thomas!

 Thomas> Look at the ACTUAL TIME.  It dropped from 0.029ms (using the index
 Thomas> scan) to 0.009ms (using a sequential scan.)

 Thomas> Index scans are not always faster, and the planner/optimizer knows
 Thomas> this.  VACUUM ANALYZE is best run when a large proportion of data
 Thomas> has been updated/loaded or in the off hours to refresh the
 Thomas> statistics on large datasets.

While I agree that generally this is true, look how stupid this 
behavior looks in this particular case: A developer creates a table
and index, knowing that the table will be large and will be intensively 
used. An admin runs 'VACUUM ANALYZE' when table is occasionally empty, 
and next, say, 1 day, until another 'VACUUM ANALYZE' starts, the index 
is simply not used! Sure you don't suppose to run 'VACUUM ANALYZE' every 
5 minutes as a solution, right?

I'm not sure if there's ever such thing like planner hints, such as,
"yes, we were switched from index back to seqscan, but this switch is
only valid until table has less than X records", but it sounds as a 
reasonable solution. 

Well anyway, here's the scenario that cannot be fought neither by
SQL programming nor by administrative guidelines, at least as I see
it. And yes, I looked on the actual time, but somehow am not moved by
how fast postgresql can seqscan an empty table, really. I believe 
there's something wrong if decisions based on a table when it is empty,
are suddenly applied when it is full.

-- 
Sincerely,
	Dmitry Karasik

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




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

* Re: VACUUM ANALYZE downgrades performance
@ 2004-12-02 16:25  Rod Taylor <pg@rbt.ca>
  parent: Dmitry Karasik <dmitry@karasik.eu.org>
  2 siblings, 1 reply; 8+ messages in thread

From: Rod Taylor @ 2004-12-02 16:25 UTC (permalink / raw)
  To: Dmitry Karasik <dmitry@karasik.eu.org>; +Cc: pgsql-performance

On Thu, 2004-12-02 at 17:07 +0100, Dmitry Karasik wrote:
> 	Hi Thomas!
> 
>  Thomas> Look at the ACTUAL TIME.  It dropped from 0.029ms (using the index
>  Thomas> scan) to 0.009ms (using a sequential scan.)
> 
>  Thomas> Index scans are not always faster, and the planner/optimizer knows
>  Thomas> this.  VACUUM ANALYZE is best run when a large proportion of data
>  Thomas> has been updated/loaded or in the off hours to refresh the
>  Thomas> statistics on large datasets.
> 
> While I agree that generally this is true, look how stupid this 
> behavior looks in this particular case: A developer creates a table
> and index, knowing that the table will be large and will be intensively 
> used. An admin runs 'VACUUM ANALYZE' when table is occasionally empty, 
> and next, say, 1 day, until another 'VACUUM ANALYZE' starts, the index 
> is simply not used! Sure you don't suppose to run 'VACUUM ANALYZE' every 
> 5 minutes as a solution, right?

You might want to try this on the next 8.0 beta to come out, or against
CVS. Tom recently applied some changes which should mitigate this
situation.

-- 




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

* Re: VACUUM ANALYZE downgrades performance
@ 2004-12-02 16:30  Steinar H. Gunderson <sgunderson@bigfoot.com>
  parent: Dmitry Karasik <dmitry@karasik.eu.org>
  2 siblings, 0 replies; 8+ messages in thread

From: Steinar H. Gunderson @ 2004-12-02 16:30 UTC (permalink / raw)
  To: pgsql-performance

On Thu, Dec 02, 2004 at 05:07:17PM +0100, Dmitry Karasik wrote:
> While I agree that generally this is true, look how stupid this 
> behavior looks in this particular case: A developer creates a table
> and index, knowing that the table will be large and will be intensively 
> used. An admin runs 'VACUUM ANALYZE' when table is occasionally empty, 
> and next, say, 1 day, until another 'VACUUM ANALYZE' starts, the index 
> is simply not used! Sure you don't suppose to run 'VACUUM ANALYZE' every 
> 5 minutes as a solution, right?

No, you run autovacuum, which automatically re-analyzes at approximately the
right time.

/* Steinar */
-- 
Homepage: http://www.sesse.net/



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

* Re: VACUUM ANALYZE downgrades performance
@ 2004-12-02 16:38  Shridhar Daithankar <ghodechhap@ghodechhap.net>
  parent: Dmitry Karasik <dmitry@karasik.eu.org>
  2 siblings, 0 replies; 8+ messages in thread

From: Shridhar Daithankar @ 2004-12-02 16:38 UTC (permalink / raw)
  To: Dmitry Karasik <dmitry@karasik.eu.org>; +Cc: pgsql-performance

On Thursday 02 Dec 2004 9:37 pm, Dmitry Karasik wrote:
> 	Hi Thomas!
>
>  Thomas> Look at the ACTUAL TIME.  It dropped from 0.029ms (using the index
>  Thomas> scan) to 0.009ms (using a sequential scan.)
>
>  Thomas> Index scans are not always faster, and the planner/optimizer knows
>  Thomas> this.  VACUUM ANALYZE is best run when a large proportion of data
>  Thomas> has been updated/loaded or in the off hours to refresh the
>  Thomas> statistics on large datasets.
>
> While I agree that generally this is true, look how stupid this
> behavior looks in this particular case: A developer creates a table
> and index, knowing that the table will be large and will be intensively
> used. An admin runs 'VACUUM ANALYZE' when table is occasionally empty,
> and next, say, 1 day, until another 'VACUUM ANALYZE' starts, the index
> is simply not used! Sure you don't suppose to run 'VACUUM ANALYZE' every
> 5 minutes as a solution, right?

Why not? If the updates are frequent enough, that is *the* solution.

But you could always use autovacuum daemon in most case.

HTH

 Shridhar



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

* Re: VACUUM ANALYZE downgrades performance
@ 2004-12-03 10:42  Dmitry Karasik <dmitry@karasik.eu.org>
  parent: Rod Taylor <pg@rbt.ca>
  0 siblings, 0 replies; 8+ messages in thread

From: Dmitry Karasik @ 2004-12-03 10:42 UTC (permalink / raw)
  To: pgsql-performance

	Hi Rod!

 Thomas> Index scans are not always faster, and the planner/optimizer knows
 Thomas> this.  VACUUM ANALYZE is best run when a large proportion of data
 Thomas> has been updated/loaded or in the off hours to refresh the
 Thomas> statistics on large datasets.
 >>  While I agree that generally this is true, look how stupid this
 >> behavior looks in this particular case: A developer creates a table and
 >> index, knowing that the table will be large and will be intensively
 >> used. An admin runs 'VACUUM ANALYZE' when table is occasionally empty,
 >> and next, say, 1 day, until another 'VACUUM ANALYZE' starts, the index
 >> is simply not used! Sure you don't suppose to run 'VACUUM ANALYZE'
 >> every 5 minutes as a solution, right?

 Rod> You might want to try this on the next 8.0 beta to come out, or
 Rod> against CVS. Tom recently applied some changes which should mitigate
 Rod> this situation.

But this would affect only VACUUM, and not ANALYZE, right?

-- 
Sincerely,
	Dmitry Karasik

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




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


end of thread, other threads:[~2004-12-03 10:42 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-30 13:30 VACUUM ANALYZE downgrades performance Dmitry Karasik <dmitry@karasik.eu.org>
2004-11-30 15:33 ` Mike Rylander <mrylander@gmail.com>
2004-11-30 15:42 ` Thomas Swan <tswan@idigx.com>
2004-12-02 16:07   ` Dmitry Karasik <dmitry@karasik.eu.org>
2004-12-02 16:25     ` Rod Taylor <pg@rbt.ca>
2004-12-03 10:42       ` Dmitry Karasik <dmitry@karasik.eu.org>
2004-12-02 16:30     ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-12-02 16:38     ` Shridhar Daithankar <ghodechhap@ghodechhap.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