pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Rob Nagler <nagler@bivio.biz>
Cc: pgsql-performance@postgresql.org
Subject: Re: vacuum locking
Date: 27 Oct 2003 12:53:56 -0500
Message-ID: <87d6ciy3bf.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <16285.17811.994000.543635@gargle.gargle.HOWL>
References: <16272.318.870000.93584@gargle.gargle.HOWL>
	<x7d6cpcdsr.fsf@yertle.int.kciLink.com>
	<16279.4988.496000.539525@gargle.gargle.HOWL>
	<200310230814.56738.mweilguni@sime.com>
	<29123.1066915615@sss.pgh.pa.us>
	<16279.61590.158000.290397@gargle.gargle.HOWL>
	<87vfqeveab.fsf@stark.dyndns.tv>
	<16281.45354.209000.280418@gargle.gargle.HOWL>
	<878ynaupgu.fsf@stark.dyndns.tv>
	<16285.17811.994000.543635@gargle.gargle.HOWL>

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




view thread (45+ messages)  latest in thread

Message-ID: <87d6ciy3bf.fsf@stark.dyndns.tv>
Permalink:  ../87d6ciy3bf.fsf@stark.dyndns.tv/
Also on:    postgresql.org/message-id/87d6ciy3bf.fsf@stark.dyndns.tv

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: gsstark@mit.edu, nagler@bivio.biz
  Subject: Re: vacuum locking
  In-Reply-To: <87d6ciy3bf.fsf@stark.dyndns.tv>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox