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: 29 Oct 2003 23:07:59 -0500
Message-ID: <87d6cffjvk.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <16288.19970.172547.319577@jump.bivio.com>
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>
	<87d6ciy3bf.fsf@stark.dyndns.tv>
	<16288.19970.172547.319577@jump.bivio.com>

Rob Nagler <nagler@bivio.biz> writes:

> One of the reason postgres is faster on the q1-4 is that postgres
> supports OFFSET/LIMIT, and oracle doesn't.  q7 and q8 are the queries
> that I've referred to recently (avg of group by).

Well the way to do offset/limit in Oracle is:

SELECT * 
  FROM (
        SELECT ... , rownum AS n 
         WHERE rownum <= OFFSET+LIMIT
       ) 
 WHERE n > OFFSET

That's basically the same thing Postgres does anyways. It actually has to do
the complete query and fetch and discard the records up to the OFFSET and then
stop when it hits the LIMIT.

> q5 and q6 are too complex to discuss here, but the fundamental issue
> is the order in which postgres decides to do things.  

That true for pretty 99% of all query optimization whether it's on Postgres or
Oracle. I'm rather curious to see the query and explain analyze output from q5
and q6.

-- 
greg




view thread (45+ messages)  latest in thread

Message-ID: <87d6cffjvk.fsf@stark.dyndns.tv>
Permalink:  ../87d6cffjvk.fsf@stark.dyndns.tv/
Also on:    postgresql.org/message-id/87d6cffjvk.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: <87d6cffjvk.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