Search Postgresql Archives

Re: Changing between ORDER BY DESC and ORDER BY ASC

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



http://www.mail-archive.com/pgsql-general@xxxxxxxxxxxxxx/msg111788.html probably won't match an index, because ASC or DESC ordering depends NOT on the table's data, but on the function parameter.

Unfortunately the planner does not recognize the following case:

CREATE TABLE "public"."prime" (
  "num" NUMERIC NOT NULL,
  CONSTRAINT "prime_pkey" PRIMARY KEY("num")
) WITH OIDS;

CREATE INDEX "prime_idx" ON "public"."prime"
  USING btree ((CASE WHEN true THEN num ELSE (- num) END));

CREATE OR REPLACE FUNCTION "public"."prime_test" (a boolean) RETURNS SETOF integer AS
$body$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        select *
        from prime
        order by case when a then num else -num end   
        limit 20
    LOOP
        RETURN NEXT rec.num;
    END LOOP;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

EXPLAIN ANALYZE select * from prime_test(true);
-- hundreds of seconds - so the index is not used

Seems the planner does not understand that "a" variable is constant "true" within the query and does not use prime_idx index (in spite of prime_idx is defined dummyly as CASE WHEN true THEN ... ELSE ... END).

William, you may try to use EXECUTE instruction with customly built query with ASC or DESC inserted.



On Mon, Aug 18, 2008 at 3:31 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
On Fri, Aug 15, 2008 at 9:35 PM, William Garrison <postgres@xxxxxxxxxxxx> wrote:
Is there an easy way to write one single query that can alternate between ASC and DESC orders?  Ex:


Take a look at this link
http://www.mail-archive.com/pgsql-general@xxxxxxxxxxxxxx/msg111788.html

--
Regards,
Sergey Konoplev


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux