Re: [PERFORM] Sorted union

Just as an FYI, if you want to reassure yourself that the ORDER BY is being applied as intended, you could do the following: ( select 1 as hint, start_time as when [...] union all select 2 as hint, end_time as when [...] ) order by seq, when This is ANSI/ISO standard, and works in PostgreSQL (

Re: [PERFORM] Sorted union

On Nov 3, 2005, at 10:21 AM, Merlin Moncure wrote: Reading the previous paragraphs I was just about to suggest this. This is a much more elegant method...you are reaping the benefits of having normalized your working set. You were trying to denormalize it back to what you were used to. Ye

Re: [PERFORM] Sorted union

> The ANSI/ISO specs are not at all ambiguous on this. An > ORDER BY is not allowed for the SELECT statements within > a UNION. It must come at the end and applied to the resulting > UNION. Interesting :/ Merlin ---(end of broadcast)--- TIP 6: e

Re: [PERFORM] Sorted union

The ANSI/ISO specs are not at all ambiguous on this. An ORDER BY is not allowed for the SELECT statements within a UNION. It must come at the end and applied to the resulting UNION. Similarly, the column names in the result come from the first query in the UNION. Column names in the query on th

Re: [PERFORM] Sorted union

> Wow. I hadn't known about generate_series, but there are a bunch of > places I've needed it. It's a wonder tool :). > But I think there is something I can do: I can just do a query of the > transaction table sorted by start time. My graph tool can keep a Reading the previous paragraphs I was

Re: [PERFORM] Sorted union

On Nov 3, 2005, at 8:20 AM, Merlin Moncure wrote: select t, (select count(*) from transaction where t between happened and when_stopped) from ( select ((generate_series(1,60) * scale)::text::interval) + '12:00 pm'::time as t ) q; Wow. I hadn't known about generate_series, but there are a b

Re: [PERFORM] Sorted union

> Merlin Moncure wrote: > > hmm, try pushing the union into a subquery...this is better style > > because it's kind of ambiguous if the ordering will apply before/after > > the union. > > Seems to be a little slower. There's a new "subquery scan" step. I figured. However it's more correct, I'm

Re: [PERFORM] Sorted union

Merlin Moncure wrote: hmm, try pushing the union into a subquery...this is better style because it's kind of ambiguous if the ordering will apply before/after the union. Seems to be a little slower. There's a new "subquery scan" step. explain analyze selectq.when_happened from (

Re: [PERFORM] Sorted union

> selectwhen_stopped as when_happened, >1 as order_hint > from transaction t > where '2005-10-25 15:00:00' <= when_stopped >and when_stopped <= '2005-10-26 10:00:00' > union all > selectwhen_stopped as when_happened, >

Re: [PERFORM] Sorted union

On 2 Nov 2005, at 21:13, Scott Lamb wrote: I want to retrieve all the events during a timespan in one list; typical timespans will involve up to a couple rows. Err, I meant up to a couple million rows. With two rows, I wouldn't be so concerned about performance. ;) -- Scott Lamb

[PERFORM] Sorted union

Using PostgreSQL 8.0.4. I've got a table with 4.5 million rows that I expect to become huge (hundred million? who knows). Each row has a start and end time. I want to retrieve all the events during a timespan in one list; typical timespans will involve up to a couple rows. If the start and