Обсуждение: BUG #5997: [queryplan] PostgreSQL is sorting the query results when the result can only be 1 item.

The following bug has been logged online:

Bug reference:      5997
Logged by:          Michiel
Email address:      michiel@ict4schools.nl
PostgreSQL version: 8.4.7
Operating system:   Linux
Description:        [queryplan] PostgreSQL is sorting the query results when
the result can only be 1 item.
Details:

PostgreSQL is sorting the query results when the result can only be 1 item.

Table scheme:
CREATE TABLE dashboards (
    id SERIAL NOT NULL PRIMARY KEY,
    uuid uuid NOT NULL,
    name varchar(50) NOT NULL,
    title varchar(100) NOT NULL,
    description text,
    approved boolean DEFAULT FALSE,
    ispublic boolean DEFAULT FALSE,
    readonly boolean DEFAULT FALSE,
    creationtime TIMESTAMP NOT NULL DEFAULT NOW(),
    modificationtime TIMESTAMP NOT NULL DEFAULT NOW(),

    UNIQUE(uuid),
    UNIQUE(name)
);

Query:
SELECT "dashboards".* FROM dashboards WHERE "dashboards"."id" = 1 ORDER BY
"dashboards"."title" ASC LIMIT 1 OFFSET 0

Explain analyze:
    Limit  (cost=8.28..8.28 rows=1 width=407) (actual time=0.084..0.084 rows=1
loops=1)
      ->  Sort  (cost=8.28..8.28 rows=1 width=407) (actual time=0.082..0.082
rows=1 loops=1)
            Sort Key: title
            Sort Method:  quicksort  Memory: 25kB
            ->  Index Scan using dashboards_pkey on dashboards  (cost=0.00..8.27
rows=1 width=407) (actual time=0.042..0.043 rows=1 loops=1)
                  Index Cond: (id = 1)

Explain analyze without ORDER BY:
    Limit  (cost=0.00..8.27 rows=1 width=407) (actual time=0.045..0.045 rows=1
loops=1)
      ->  Index Scan using dashboards_pkey on dashboards  (cost=0.00..8.27
rows=1 width=407) (actual time=0.041..0.041 rows=1 loops=1)
            Index Cond: (id = 1)

What i see and expect:

I see that the query is sorted. But the result is max 1 item. Because id is
a primary key, which is unique. Which always results in 1 row. Then i whould
say that the query optimiser could leave the sorting out of it.

so for the query optimiser it should be:
  if (rows > 1) sort();

this is also for selecting on unique columns. Because sorting 1 row is
nonsence.


ps. The query is rendered by an ORM, so not custom made. Else I would leave
the ORDER BY out of it.

Can someone also test this on version 9.0.3?

"Michiel" <michiel@ict4schools.nl> wrote:

> [query with ORDER BY clause]

> [plan which spends 0.039 ms sorting one row]

> What i see and expect:
>
> I see that the query is sorted. But the result is max 1 item.

This is not a bug; it is a feature request for the planner.

If you see a way that a query can be reliably analyzed to determine
that the sort can be omitted which takes less than the 0.039 ms
required for the sort step, please suggest it.  But not on the bugs
list.

-Kevin