Re: Possible optimisation: push down SORT and LIMIT nodes

On Wed, May 30, 2018 at 03:46:40PM +0000, Christopher Wilson wrote:
> We have a query which is rather slow (about 10 seconds), and it looks like this:
>
> The inventory table has the quantity of each asset in the inventory on each
> date (complete SQL to create and populate the tables with dummy data is
> below). The query plan looks like this (the non-parallel version is similar):

Hi,

Thanks for including the test case.

> Limit (cost=217591.77..217603.60 rows=100 width=32) (actual time=9122.235..9122.535 rows=100 loops=1)
...
> -> Sort (cost=216591.73..220628.83 rows=1614839 width=32) (actual time=8879.909..8880.030 rows=727 loops=4)
> Sort Key: inventory.date, asset.name
> Sort Method: external merge Disk: 50904kB
> Buffers: shared hit=27365, temp read=25943 written=25947

Yep, the sort is expensive and largely wasted..

> I'm imagining something like a sort-limit-finish node, which sorts its input
> and then returns at least the limit number of rows, but keeps returning rows
> until it exhausts the last sort prefix that it read.
[...]
> Does this sound correct, reasonable and potentially interesting to Postgres
> developers?

I think your analysis may be (?) unecessarily specific to your specific problem
query.

For diagnostic purposes, I was able to to vastly improve the query runtime with
a CTE (WITH):

|postgres=# explain(analyze,buffers) WITH x AS (SELECT inventory.date, asset.name, inventory.quantity FROM temp.inventory LEFT JOIN temp.asset ON asset.id=id_asset LIMIT 99) SELECT * FROM x ORDER BY date, name;
| Sort (cost=1090.60..1090.85 rows=99 width=40) (actual time=3.764..3.988 rows=99 loops=1)
| Sort Key: x.date, x.name
| Sort Method: quicksort Memory: 32kB
| Buffers: shared hit=298
| CTE x
| -> Limit (cost=0.28..889.32 rows=99 width=31) (actual time=0.063..2.385 rows=99 loops=1)
| Buffers: shared hit=298
| -> Nested Loop Left Join (cost=0.28..44955006.99 rows=5006001 width=31) (actual time=0.058..1.940 rows=99 loops=1)
| Buffers: shared hit=298
| -> Seq Scan on inventory (cost=0.00..5033061.00 rows=5006001 width=12) (actual time=0.020..0.275 rows=99 loops=1)
| Buffers: shared hit=1
| -> Index Scan using asset_pkey on asset (cost=0.28..7.98 rows=1 width=27) (actual time=0.008..0.008 rows=1 loops=99)
| Index Cond: (id = inventory.id_asset)
| Buffers: shared hit=297
| -> CTE Scan on x (cost=0.00..198.00 rows=99 width=40) (actual time=0.073..2.989 rows=99 loops=1)
| Buffers: shared hit=298
| Planning time: 0.327 ms
| Execution time: 4.260 ms

It's not clear to me if there's some reason why the planner couldn't know to
use a similar plan (sort-limit-... rather than limit-sort-...)

Justin