Distinct on is working really well! If I need to be able to index something
I might start thinking along those lines.
On Fri, Jan 21, 2011 at 12:13 PM, Robert Haas wrote:
> On Fri, Jan 14, 2011 at 8:50 PM, Nikolas Everett
> wrote:
> >
> >
> > On Fri, Jan 14, 2011 at 7:59 PM, Kevin Grittner
> >
On Fri, Jan 14, 2011 at 8:50 PM, Nikolas Everett wrote:
>
>
> On Fri, Jan 14, 2011 at 7:59 PM, Kevin Grittner
> wrote:
>>
>> Tom Lane wrote:
>>
>> > Shaun's example is a bit off
>>
>> > As for speed, either one might be faster in a particular
>> > situation.
>>
>> After fixing a mistake in my te
> After trying both against the real tables DISTINCT ON seems to be
> about two orders of magnitude faster than the other options.
Glad it worked. It looked at least naively similar to situations I've run into
and DISTINCT ON always helped me out. It's all the fun of GROUP BY with the
ability t
> Shaun's example is a bit off: normally, when using DISTINCT ON, you want
> an ORDER BY key that uses all the given DISTINCT keys and then some
> more. To get the max revision for each a/b combination it ought to be
Hah, well i figured I was doing something wrong. I just thought about it a
lit
On Fri, Jan 14, 2011 at 7:59 PM, Kevin Grittner wrote:
> Tom Lane wrote:
>
> > Shaun's example is a bit off
>
> > As for speed, either one might be faster in a particular
> > situation.
>
> After fixing a mistake in my testing and learning from Tom's example
> I generated queries against the OP'
Tom Lane wrote:
> Shaun's example is a bit off
> As for speed, either one might be faster in a particular
> situation.
After fixing a mistake in my testing and learning from Tom's example
I generated queries against the OP's test data which produce
identical results, and I'm finding no signi
"Kevin Grittner" writes:
> Shaun Thomas wrote:
>> This actually looks like a perfect candidate for DISTINCT ON.
>>
>> SELECT DISTINCT ON (a, b) a, b, revision
>> FROM test
>> ORDER BY a, b DESC;
> I wouldn't say perfect. It runs about eight times slower than what
> I suggested and returns a f
Shaun Thomas wrote:
> This actually looks like a perfect candidate for DISTINCT ON.
>
> SELECT DISTINCT ON (a, b) a, b, revision
>FROM test
> ORDER BY a, b DESC;
I wouldn't say perfect. It runs about eight times slower than what
I suggested and returns a fairly random value for revisio
On 01/14/2011 03:17 PM, Nikolas Everett wrote:
SELECT *
FROM request
JOIN (SELECT a, MAX(b) as b FROM test GROUP BY a) max USING (a)
JOIN test USING (a, b);
This actually looks like a perfect candidate for DISTINCT ON.
SELECT DISTINCT ON (a, b) a, b, revision
FROM test
ORDER BY a,
Nikolas Everett wrote:
> I'm really going to be doing this with an arbitrary list of As.
OK, how about this?:
CREATE TEMPORARY TABLE request (a INTEGER NOT NULL);
INSERT INTO request SELECT a FROM generate_series(2, 200) AS t(a);
ANALYZE request;
SELECT y.*
from (select a, max(revision) as
On Fri, Jan 14, 2011 at 5:30 PM, Kevin Grittner wrote:
> SELECT y.*
> from (select a, max(revision) as revision
> from test where a between 2 and 200
> group by a) x
> join test y using (a, revision);
While certainly simpler than my temp table this really just exposes a flaw
Nikolas Everett wrote:
> Am I missing something or is this really the best way to do this in
8.3?
How about this?:
SELECT y.*
from (select a, max(revision) as revision
from test where a between 2 and 200
group by a) x
join test y using (a, revision);
-Kevin
--
Sen
I'm using 8.3 and I have a table that contains many revisions of the same
entity and I have a query that is super slow. Please help! I'm going to
paste in some SQL to set up test cases and some plans below. If that isn't
the right way to post to this list please let me know and I'll revise.
My
13 matches
Mail list logo
