Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem using LIMIT 1 when only 1 record exists.

Problem using LIMIT 1 when only 1 record exists.

From
"Steve Thames"
Date:
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

Re: Problem using LIMIT 1 when only 1 record exists.

From
Neil Conway
Date:
"Steve Thames" <steve@softlife.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

Re: Problem using LIMIT 1 when only 1 record exists.

From
Tom Lane
Date:
Neil Conway <neilc@samurai.com> writes:
> "Steve Thames" <steve@softlife.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