Re: Changing between ORDER BY DESC and ORDER BY ASC

http://www.mail-archive.com/pgsql-general(at)postgresql(dot)org/msg111788.htmlprobably
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(dot)ru(at)gmail(dot)com> wrote:

> On Fri, Aug 15, 2008 at 9:35 PM, William Garrison <postgres(at)mobydisk(dot)com>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(at)postgresql(dot)org/msg111788.html
>
> --
> Regards,
> Sergey Konoplev
>