Re: 7.1.3 not using index

Daniel Kalchev <daniel(at)digsys(dot)bg> writes:
> Here is another table:

> CREATE TABLE "persons" (
> "personid" integer DEFAULT nextval('personid_seq'::text),
> "name" text,
> "title" text,
> [...]
> );

> CREATE INDEX "persons_personid_idx" on "persons" using btree ( "personid"
> "int4_ops" );

> (part of the statistics for this row)
> attname | attdispersion | starelid | staattnum | staop | stanullfrac |
> stacommonfrac | stacommonval | staloval |
> stahival
> personid | 4.1328e-05 | 19795 | 1 | 97 | 0 |
> 0.000206469 | 2089 | 1 | 12857

> now, EXPLAIN again gives me:

> db=# explain select * from persons where personid = 1;
> NOTICE: QUERY PLAN:

> Seq Scan on persons (cost=0.00..490.62 rows=1 width=177)

That does seem pretty broken; the thing is well aware that the query is
selective (note the rows estimate), so why is it not using the index?

Do you get the same plan if you try to force an indexscan by doing
set enable_seqscan to off;

Also, I'd like to see the EXPLAIN VERBOSE result not just EXPLAIN.

> db=# explain select * from persons, r where r.d = 1 and r.a = persons.personid;
> NOTICE: QUERY PLAN:

> Merge Join (cost=0.00..nan rows=299 width=193)
> -> Index Scan using persons_personid_idx on persons (cost=0.00..nan
> rows=14530 width=177)
> -> Index Scan using r_a_idx on representatives (cost=0.00..nan rows=719
> width=16)

> Why would it do index scans on r.a?

To get the data in the right order for a merge join. However, I think
the really interesting part of this is the "cost=0.00..nan" bit.
Apparently you're getting some NaN results during computation of the
cost estimates, which will completely screw up all the planner's
estimates of which plan is cheapest. That needs to be looked at.
We've seen previous reports of 7.1 getting confused that way when there
were column min or max values of +/-infinity in timestamp columns ...
but it looks like these are plain integer columns, so there's something
else going on.

One thing that should be eliminated at the outset is the possibility of
a bad build of Postgres. How did you configure and build, *exactly*?
Did you make any midcourse corrections (like building some of the files
with different compiler switches than others)?

regards, tom lane