Rob Nagler <nagler@bivio.biz> writes:
> I didn't find ALTER SESSION for postgres (isn't that Oracle?), so I
> set sort_mem in the conf file to 512000, restarted postrgres. Reran
> the simpler query (no name) 3 times, and it was still 27 secs.
Sorry, I don't know how that bubbled up from the depths of my Oracle memory.
In postgres it's just "SET"
db=> set sort_mem = 512000;
SET
> > To convince it to do the right thing you would have to do either:
> >
> > SELECT a, t2.name
> > FROM (SELECT avg(f1),f2 FROM t1 GROUP BY f2) AS t1
> > JOIN t2 USING (f2)
> >
> > Or use a subquery:
> >
> > SELECT a, (SELECT name FROM t2 WHERE t2.f2 = t1.f2)
> > FROM t1
> > GROUP BY f2
>
> This doesn't solve the problem. It's the GROUP BY that is doing the
> wrong thing. It's grouping, then aggregating.
But at least in the form above it will consider using an index on f2, and it
will consider using indexes on t1 and t2 to do the join.
It's unlikely to go ahead and use the indexes though because normally sorting
is faster than using the index when scanning the whole table. You should
compare the "explain analyze" results for the original query and these two.
And check the results with "set enable_seqscan = off" as well.
I suspect you'll find your original query uses sequential scans even when
they're disabled because it has no alternative. With the two above it can use
indexes but I suspect you'll find they actually take longer than the
sequential scan and sort -- especially if you have sort_mem set large enough.
--
greg