Re: Question whether this is a known problem in 7.1.2

By the way, a colleague just reproduced this problem on a 7.2.1 postgres.

-----Original Message-----
From: Rachit Siamwalla [mailto:rachit(at)ensim(dot)com]
Sent: Friday, June 07, 2002 4:27 PM
To: pgsql-hackers; Paul Menage
Subject: [HACKERS] Question whether this is a known problem in 7.1.2

This problem was discovered in 7.1.2. Was wondering whether this is a known problem or not; we plan to test this on the latest postgres sometime later.

We have a large table, lets call it A, millions of rows. And in the table is a field called time, which is TIMESTAMP type. We have an index on it.

Oftentimes we like to get the latest row inserted by time on a given constraint. So we do a:

SELECT * FROM A WHERE someconstraint = somerandomnumber ORDER BY time desc limit 1;

Postgres intellegently uses the index to scan through the table from the end forward.

If there are no items that fit the constraint, the query will take a long time (cause it has to scan the whole table).

If there are items (plural important here, read below) that fit the constraint, the database finds the first item, and returns it right away (fairly quickly if the item is near the end).

However, if there is only ONE item, postgres still scans the whole database. Not sure why. We also find out that if:

There are 2 items that match the criteria, and you do a LIMIT 2, it scans the whole table as well. Limit 1 returns quickly. Basically it seems like postgres is looking for one more item than it needs to.

-rchit

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster