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.
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:Take a look at this link
Is there an easy way to write one single query that can alternate between ASC and DESC orders? Ex:
http://www.mail-archive.com/pgsql-general@xxxxxxxxxxxxxx/msg111788.html
--
Regards,
Sergey Konoplev
