Re: Unexpected sequential scan on an indexed column - Mailing list pgsql-performance

From Tom Lane
Subject Re: Unexpected sequential scan on an indexed column
Date
Msg-id 25680.1258328036@sss.pgh.pa.us
Whole thread Raw
In response to Unexpected sequential scan on an indexed column  (Eddy Escardo-Raffo <eescardo@kikini.com>)
Responses Re: Unexpected sequential scan on an indexed column  (Eddy Escardo-Raffo <eescardo@kikini.com>)
List pgsql-performance
Eddy Escardo-Raffo <eescardo@kikini.com> writes:
> The table used in this query is called "users", and it has columns "userid"
> (primary key) and "location".
> The "location" column is indexed.
> The users table has 1 million rows, and all rows have integer typed value
> '-1' for  "location" column, except for 2 rows that have the integer value
> '76543'.

Oh, after poking at it a bit more, I realize the problem: the planner
doesn't want to use an indexscan because it assumes there's a
significant probability that the search will be for -1 (in which case
the indexscan would be slower than a seqscan, as indeed your results
prove).  Even though it could know in this particular case that the
comparison value isn't -1, I doubt that teaching it that would help your
real queries where it will probably be impossible to determine the
comparison values in advance.

I would suggest considering using NULL rather than inventing a dummy
value for unknown locations.  The estimation heuristics will play a
lot nicer with that choice.

            regards, tom lane

pgsql-performance by date:

Previous
From: david@lang.hm
Date:
Subject: Re: limiting performance impact of wal archiving.
Next
From: Eddy Escardo-Raffo
Date:
Subject: Re: Unexpected sequential scan on an indexed column