Re: Optimizer questions - Mailing list pgsql-hackers

On Fri, Jan 8, 2016 at 11:58 AM, Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:

Attached please find improved version of the optimizer patch for LIMIT clause.
Now I try to apply this optimization only for non-trivial columns requiring evaluation.
May be it will be better to take in account cost of this columns evaluation but right now I just detect non-variable columns.

We may think about more general feature: LIMIT pushdown. In the Konstantin's patch planner push LIMIT before tlist calculation.

But there are other cases when calculating LIMIT first would be beneficial. For instance, we can do LIMIT before JOINs. That is possible only for LEFT JOIN which is not used in filter and ordering clauses. See the example below.

# create table t1 as (select i, random() v from generate_series(1,1000000) i);

SELECT 1000000

# create table t2 as (select i, random() v from generate_series(1,1000000) i);

SELECT 1000000

# explain analyze select * from t1 left join t2 on t1.i = t2.i order by t1.v limit 10;

                                                              QUERY PLAN

------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=87421.64..87421.67 rows=10 width=24) (actual time=1486.276..1486.278 rows=10 loops=1)

   ->  Sort  (cost=87421.64..89921.64 rows=1000000 width=24) (actual time=1486.275..1486.275 rows=10 loops=1)

         Sort Key: t1.v

         Sort Method: top-N heapsort  Memory: 25kB

         ->  Hash Left Join  (cost=27906.00..65812.00 rows=1000000 width=24) (actual time=226.180..1366.238 rows=1000000

               Hash Cond: (t1.i = t2.i)

               ->  Seq Scan on t1  (cost=0.00..15406.00 rows=1000000 width=12) (actual time=0.010..77.041 rows=1000000 l

               ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12) (actual time=226.066..226.066 rows=1000000 loop

                     Buckets: 131072  Batches: 1  Memory Usage: 46875kB

                     ->  Seq Scan on t2  (cost=0.00..15406.00 rows=1000000 width=12) (actual time=0.007..89.002 rows=100

 Planning time: 0.123 ms

 Execution time: 1492.118 ms

(12 rows)

# explain analyze select * from (select * from t1 order by t1.v limit 10) t1 left join t2 on t1.i = t2.i;

                                                              QUERY PLAN

------------------------------------------------------------------------------------------------------------------------

 Hash Right Join  (cost=37015.89..56171.99 rows=10 width=24) (actual time=198.478..301.278 rows=10 loops=1)

   Hash Cond: (t2.i = t1.i)

   ->  Seq Scan on t2  (cost=0.00..15406.00 rows=1000000 width=12) (actual time=0.005..74.303 rows=1000000 loops=1)

   ->  Hash  (cost=37015.77..37015.77 rows=10 width=12) (actual time=153.584..153.584 rows=10 loops=1)

         Buckets: 1024  Batches: 1  Memory Usage: 1kB

         ->  Limit  (cost=37015.64..37015.67 rows=10 width=12) (actual time=153.579..153.580 rows=10 loops=1)

               ->  Sort  (cost=37015.64..39515.64 rows=1000000 width=12) (actual time=153.578..153.578 rows=10 loops=1)

                     Sort Key: t1.v

                     Sort Method: top-N heapsort  Memory: 25kB

                     ->  Seq Scan on t1  (cost=0.00..15406.00 rows=1000000 width=12) (actual time=0.012..78.828 rows=100

 Planning time: 0.132 ms

 Execution time: 301.308 ms

(12 rows)

In this example LIMIT pushdown makes query 5 times faster. It would be very nice if optimizer make this automatically.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company