Re: Optimizer questions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: konstantin knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
Subject: Re: Optimizer questions
Date: 2016-03-11 01:45:34
Message-ID: 28389.1457660734@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

I wrote:
> As far as that goes, it seems to me after thinking about it that
> non-sort-column tlist items containing SRFs should always be postponed,
> too. Performing a SRF before sorting bloats the sort data vertically,
> rather than horizontally, but it's still bloat. (Although as against
> that, when you have ORDER BY + LIMIT, postponing SRFs loses the ability
> to use a bounded sort.) The killer point though is that unless the sort
> is stable, it might cause surprising changes in the order of the SRF
> output values. Our sorts aren't stable; here's an example in HEAD:

> # select q1, generate_series(1,9) from int8_tbl order by q1 limit 7;
> q1 | generate_series
> -----+-----------------
> 123 | 2
> 123 | 3
> 123 | 4
> 123 | 5
> 123 | 6
> 123 | 7
> 123 | 1
> (7 rows)

> I think that's pretty surprising, and if we have an opportunity to
> provide more intuitive semantics here, we should do it.

Here's an updated patch (requires current HEAD) that takes care of
window functions correctly and also does something reasonable with
set-returning functions:

# explain verbose select q1, generate_series(1,9) from int8_tbl order by q1 limit 7;
QUERY PLAN
---------------------------------------------------------------------------------
Limit (cost=1.11..1.14 rows=7 width=12)
Output: q1, (generate_series(1, 9))
-> Result (cost=1.11..26.16 rows=5000 width=12)
Output: q1, generate_series(1, 9)
-> Sort (cost=1.11..1.12 rows=5 width=8)
Output: q1
Sort Key: int8_tbl.q1
-> Seq Scan on public.int8_tbl (cost=0.00..1.05 rows=5 width=8)
Output: q1
(9 rows)

# select q1, generate_series(1,9) from int8_tbl order by q1 limit 7;
q1 | generate_series
-----+-----------------
123 | 1
123 | 2
123 | 3
123 | 4
123 | 5
123 | 6
123 | 7
(7 rows)

I added some user-facing documentation too. I think this is committable,
though maybe we should add a regression test case or two.

regards, tom lane

Attachment Content-Type Size
postpone-tlist-2.patch text/x-diff 23.7 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Petr Jelinek 2016-03-11 01:54:22 Re: Relation extension scalability
Previous Message Dilip Kumar 2016-03-11 01:44:21 Re: Relation extension scalability