Skip site navigation (1) Skip section navigation (2)

Re: Indexes not always used after inserts/updates/vacuum

From: Reinhard Max <max(at)suse(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Michael G(dot) Martin" <michael(at)vpmonline(dot)com>,<pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Indexes not always used after inserts/updates/vacuum
Date: 2002-02-28 11:56:09
Message-ID: Pine.LNX.4.44.0202281243330.6623-100000@Wotan.suse.de (view raw or flat)
Thread:
Lists: pgsql-bugs
On Wed, 27 Feb 2002 at 22:57, Tom Lane wrote:

> Also, to put the rubber to the road: if you force an indexscan by
> doing "set enable_seqscan = off", does it get faster or slower?
> (EXPLAIN ANALYZE would be useful here.)

I've just found a case where forcing indexscans results in much higher
speed. On the the 350000 rows table mentioned in my other post after a
"VACUUM FULL ANALYZE":

max=# set enable_seqscan to false;
max=# EXPLAIN analyze
	SELECT count(foo.id) FROM foo, bar
	WHERE foo.id = bar.ref2foo;
Aggregate	(cost=27513.65..27513.65 rows=1 width=8)
		(actual time=652.38..652.38 rows=1 loops=1)
  ->  Merge Join
		(cost=0.00..27417.57 rows=38431 width=8)
		(actual time=0.06..603.02 rows=38431 loops=1)
        ->  Index Scan using foo_pkey on foo
		(cost=0.00..25153.18 rows=352072 width=4)
		(actual time=0.03..157.57 rows=38432 loops=1)
        ->  Index Scan using idx_bar_ref2foo on bar
		(cost=0.00..807.74 rows=38431 width=4)
		(actual time=0.02..170.25 rows=38431 loops=1)
Total runtime: 652.58 msec
               ^^^^^^^^^^^
max=# set enable_seqscan to true;
max=# EXPLAIN analyze
	SELECT count(foo.id) FROM foo, bar
	WHERE foo.id = bar.ref2foo;

Aggregate
		(cost=18560.65..18560.65 rows=1 width=8)
		(actual time=4951.57..4951.57 rows=1 loops=1)
  ->  Hash Join
		(cost=911.39..18464.58 rows=38431 width=8)
		(actual time=653.26..4905.37 rows=38431 loops=1)
        ->  Seq Scan on foo
		(cost=0.00..9251.72 rows=352072 width=4)
		(actual time=0.02..769.60 rows=352072 loops=1)
        ->  Hash
		(cost=683.31..683.31 rows=38431 width=4)
		(actual time=140.60..140.60 rows=0 loops=1)
              ->  Seq Scan on bar
		(cost=0.00..683.31 rows=38431 width=4)
		(actual time=0.02..78.57 rows=38431 loops=1)
Total runtime: 4951.70 msec
               ^^^^^^^^^^^^

I've reproduced that several times. Even on a newly started postmaster
the query takes less than 2.5 seconds with seqscans swited off.

cu
	Reinhard


In response to

Responses

pgsql-bugs by date

Next:From: Michael G. MartinDate: 2002-02-28 14:40:15
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze
Previous:From: NiteshDate: 2002-02-28 06:25:22
Subject: postmaster problem....

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group