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

Re: index vs. seq scan choice?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "George Pavlov" <gpavlov(at)mynewplace(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: index vs. seq scan choice?
Date: 2007-05-25 01:08:11
Message-ID: 27828.1180055291@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-www
"George Pavlov" <gpavlov(at)mynewplace(dot)com> writes:
> I am curious what could make the PA query to ignore the index. What are
> the specific stats that are being used to make this decision?

The frequency of the specific value being searched for, and the overall
order-correlation of the column.  Since the latter is not dependent on a
particular value, my guess at the reason for the inconsistent results is
that you don't have the column's statistics target set high enough to
track all the interesting values --- or maybe just not high enough to
acquire sufficiently accurate frequency estimates for them.  Take a look
at the pg_stats row for the column ...

(The default statistics target is 10, which is widely considered too
low --- you might find 100 more suitable.)

			regards, tom lane

In response to

Responses

pgsql-www by date

Next:From: Alvaro HerreraDate: 2007-05-25 02:39:22
Subject: Re: index vs. seq scan choice?
Previous:From: George PavlovDate: 2007-05-24 23:15:54
Subject: index vs. seq scan choice?

pgsql-general by date

Next:From: Scott RibeDate: 2007-05-25 01:15:49
Subject: Uhm, so, yeah, speaking of /.
Previous:From: Andrew SullivanDate: 2007-05-25 00:39:11
Subject: Re: why postgresql over other RDBMS

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