Re: Patch for removng unused targets

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-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)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.