Problem using LIMIT 1 when only 1 record exists.

I have a table with 14,655,100 rows in it. No unique keys.
I have an index on the fields "symbol", varchar[12], and "qdate", int4 as
format=YYYYMMDD.
I have a field called "expmoyr", char[6] as format=Mmm-YY.
I have a field called "last", float4.

The SQL command:
SELECT last FROM table WHERE symbol='Symbol' AND expmoyr='Mmm-YY' ORDER BY
qdate DESC LIMIT 1;

This query works fine when there is more than one record meeting the
criteria. When there is only 1, the query locks and no result is returned.

I have tried vacuuming and reindexing the entire database several times.
Please let me know if there is a fix to this.

Thanks.
Steve Thames
Softlife Consulting, Inc.
530-672-6908
www.softlife.com

"Steve Thames" <steve(at)softlife(dot)com> writes:
> The SQL command:
> SELECT last FROM table WHERE symbol='Symbol' AND expmoyr='Mmm-YY' ORDER BY
> qdate DESC LIMIT 1;
>
> This query works fine when there is more than one record meeting the
> criteria. When there is only 1, the query locks and no result is
> returned.

I'm skeptical that LIMIT is broken. Can you provide a reproducible
example?

Also, what version of PostgreSQL is this?

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> "Steve Thames" <steve(at)softlife(dot)com> writes:
>> The SQL command:
>> SELECT last FROM table WHERE symbol='Symbol' AND expmoyr='Mmm-YY' ORDER BY
>> qdate DESC LIMIT 1;
>>
>> This query works fine when there is more than one record meeting the
>> criteria. When there is only 1, the query locks and no result is
>> returned.

> I'm skeptical that LIMIT is broken. Can you provide a reproducible
> example?

I'll bet very long odds that "locks" should be read as "ran longer than
Steve wanted to wait". Before this 7.4 change:

2002-11-22 17:10 tgl

* src/: backend/executor/nodeLimit.c, include/nodes/execnodes.h:
Redesign internal logic of nodeLimit so that it does not need to
fetch one more row from the subplan than the COUNT would appear to
require. This costs a little more logic but a number of people
have complained about the old implementation.

nodeLimit would have tried to fetch two rows from the underlying plan.
If only one matching row is actually available, this would result in
scanning the whole source table.

regards, tom lane