"Gene Sokolov" <hook@aktrad.ru> writes:
> From: Tom Lane <tgl@sss.pgh.pa.us>
>> You might be able to hack around the issue with queries like
>> SELECT x FROM table ORDER BY x LIMIT 1;
>> SELECT x FROM table ORDER BY x DESC LIMIT 1;
> It is a real show stopper. No luck completely, the indexes are ignored:
> bars=> explain select id from itemsbars order by id limit 1;
> NOTICE: QUERY PLAN:
> Sort (cost=44404.41 rows=969073 width=4)
> -> Seq Scan on itemsbars (cost=44404.41 rows=969073 width=4)
Yes, you missed my comment that 6.5.* needs some help or it won't
consider an index scan at all. This gives the right sort of plan:
regression=> explain select id from itemsbars where id > 0 order by id limit 1;
NOTICE: QUERY PLAN:
Index Scan using itemsbars_id_key on itemsbars (cost=21.67 rows=334 width=4)
The WHERE clause can be chosen so that it won't actually exclude
anything, but there has to be a WHERE clause that looks useful with
an index or an indexscan plan won't even get generated. (Also,
the DESC case doesn't work in 6.5.*, unless you apply the backwards-
index-scan patch that Hiroshi posted a few weeks ago.)
This is fixed in current sources, BTW:
regression=> explain select id from itemsbars order by id limit 1;
NOTICE: QUERY PLAN:
Index Scan using bars_id on itemsbars (cost=62.00 rows=1000 width=4)
regression=> explain select id from itemsbars order by id desc ;
NOTICE: QUERY PLAN:
Index Scan Backward using bars_id on itemsbars (cost=62.00 rows=1000 width=4)
although we still need to do some rejiggering of the cost estimation
rules; current sources are probably *too* eager to use an indexscan.
regards, tom lane
