Skip site navigation (1) Skip section navigation (2)

Patch for removng unused targets

From: Alexander Korotkov <aekorotkov(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Patch for removng unused targets
Date: 2012-10-02 07:45:56
Message-ID: CAPpHfdtG5qoHoD+w=Tz3wC3fZ=b8i21=V5xandBFM=DTo-Yg=Q@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi!

Attached patch removes unused targets which are used only for order by when
data already comes in right order. It introduces resorderbyonly flag of
TargetEntry which indicated that entry is used only for ORDER BY clause. If
data comes in right order then such entries are removed in grouping_planner
function.

This is my first patch on planner. Probably, I did it in wrong way. But I
think it is worthwhile optimization and you could give me direction to
rework patch.

Actually we meet need of this optimization when ranking full-text search in
GIN index (it isn't published yet, will post prototype soon). But there is
some synthetic example illustrating benefit from patch.

CREATE OR REPLACE FUNCTION slow_func(x float8, y float8) RETURNS float8 AS
$$
BEGIN
PERFORM pg_sleep(0.01);
    RETURN x + y;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE TABLE test AS (SELECT random() AS x, random() AS y FROM
generate_series(1,1000));
CREATE INDEX test_idx ON test(slow_func(x,y));

Without patch:

test=# EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM test ORDER BY
slow_func(x,y) LIMIT 10;
                                                              QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3.09 rows=10 width=16) (actual time=11.344..103.443
rows=10 loops=1)
   Output: x, y, (slow_func(x, y))
   ->  Index Scan using test_idx on public.test  (cost=0.00..309.25
rows=1000 width=16) (actual time=11.341..103.422 rows=10 loops=1)
         Output: x, y, slow_func(x, y)
 Total runtime: 103.524 ms
(5 rows)

With patch:

test=# EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM test ORDER BY
slow_func(x,y) LIMIT 10;
                                                            QUERY PLAN


-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3.09 rows=10 width=16) (actual time=0.062..0.093
rows=10 loops=1)
   Output: x, y
   ->  Index Scan using test_idx on public.test  (cost=0.00..309.25
rows=1000 width=16) (actual time=0.058..0.085 rows=10 loops=1)
         Output: x, y
 Total runtime: 0.164 ms
(5 rows)

------
With best regards,
Alexander Korotkov.

Attachment: unused-targets-1.patch
Description: application/octet-stream (4.2 KB)

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2012-10-02 08:05:06
Subject: Re: date_in and buffer overrun
Previous:From: Amit kapilaDate: 2012-10-02 07:43:50
Subject: Re: BUG #7534: walreceiver takes long time to detect n/w breakdown

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group