Re: Changing between ORDER BY DESC and ORDER BY ASC

http://www.mail-archive.com/pgsql-general@postgresql.org/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.