Sorry for the delay. I've reviewed the patch. It was applied successfully, and it worked well for tests I did including the example you showed. I think it's worth the work, but I'm not sure you go about it in the right way. (I feel the patch decreases code readability more than it gives an advantage.) If you move forward in this way, I think the following need to be considered at least:
* The following functions need to be changed to have the resorderbyonly flag: _equalTargetEntry() _readTargetEntry() _outTargetEntry() * Can we remove the attributes in the coded way safely? /* * Plan come out in the right order, we can remove attributes which * are used only for ORDER BY clause because there is no need to * calculate them. */ The implicit relationship between the TargetEntry's resno and the list size (the resno is not larger than the list size if I understand it aright) might break. Is that OK? (I would like to think a more simple approach to this optimization.) Thanks, Best regards, Etsuro Fujita From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Alexander Korotkov Sent: Tuesday, October 02, 2012 4:46 PM To: pgsql-hackers; Tom Lane Subject: [HACKERS] Patch for removng unused targets 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.
