From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Eddy Escardo-Raffo <eescardo(at)kikini(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Unexpected sequential scan on an indexed column |
Date: | 2009-11-15 23:33:56 |
Message-ID: | 25680.1258328036@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Eddy Escardo-Raffo <eescardo(at)kikini(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Eddy Escardo-Raffo | 2009-11-15 23:59:31 | Re: Unexpected sequential scan on an indexed column |
Previous Message | Tom Lane | 2009-11-15 23:05:50 | Re: Unexpected sequential scan on an indexed column |