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 | Resend email |
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 |
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 |