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

Re: Inconsistant use of index.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ron Mayer <ron(at)intervideo(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Inconsistant use of index.
Date: 2002-03-26 17:37:49
Message-ID: 660.1017164269@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Ron Mayer <ron(at)intervideo(dot)com> writes:
>   Once some of my tables started getting pretty large, PostgreSQL
> suddenly stopped using indexes when I use expressions like "col = value"
> decreasing performance by 20X.

Hmm.  The EXPLAIN shows that the planner is not doing too badly at
estimating the number of rows involved:

> logs2=# explain analyze select count(*) from fact where dat='2002-03-01';
> NOTICE:  QUERY PLAN:

> Aggregate  (cost=375631.14..375631.14 rows=1 width=0) (actual
> time=76689.42..76689.42 rows=1 loops=1)
>   ->  Seq Scan on fact  (cost=0.00..375101.72 rows=211765 width=0) (actual
> time=20330.96..76391.94 rows=180295 loops=1)
> Total runtime: 76707.92 msec

212K estimate for 180K real is not bad at all.  So the problem is in the
cost models not the initial row count estimation.

If you force an indexscan via "set enable_seqscan to off", what does
EXPLAIN ANALYZE report?

Also, what do you get from
	select * from pg_stats where tablename = 'fact';
I'm particularly interested in the correlation estimate for the dat
column.  (Would you happen to have an idea whether the data has been
inserted more-or-less in dat order?)

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-03-26 17:46:23
Subject: Re: postmaster crash
Previous:From: Michael BeckstetteDate: 2002-03-26 17:18:02
Subject: postmaster crash

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