Timeline for Postgresql / split one record on two rows depending of two fields
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 7, 2019 at 8:54 | vote | accept | ceadreak | ||
| Jul 6, 2017 at 15:37 | comment | added | ceadreak |
I confirm, the fasted (~50% compared to UNION) is LATERAL, in my case. Thanks for this addition
|
|
| Jul 6, 2017 at 14:46 | comment | added | Lennart - Slava Ukraini | I could not edit my comment so I'll add a new one. I did a similar test for DB2 as yours, and the time to execute the queries where about the same. The estimated cost for the LATERAL query where about 60% of the UNION query and the number of I/O:s where also reduced to about half. | |
| Jul 6, 2017 at 14:34 | comment | added | Lennart - Slava Ukraini | I haven't used LATERAL with Postgresql but for DB2 the number of rows read is usually significantly reduced. In a case like this it probably does not matter at all so I just mentioned it as a side note. | |
| Jul 6, 2017 at 14:32 | comment | added | Evan Carroll |
test: CREATE TABLE foo AS SELECT x::int AS x1, trunc(x*3/7)::int AS x2 FROM generate_series(1,1e6) AS t(x); q1: SELECT x1 FROM foo UNION ALL SELECT x2 FROM foo; q2: SELECT x FROM foo CROSS JOIN LATERAL ( VALUES (x1),(x2) ) AS t(x); q2 is twice as slow as q1.
|
|
| Jul 6, 2017 at 14:27 | comment | added | Evan Carroll | I think the performance would be substantially detrimental under all workloads. LATERAL isn't exactly fast. | |
| Jul 6, 2017 at 14:17 | history | answered | Lennart - Slava Ukraini | CC BY-SA 3.0 |