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
copy link · copy postgr.es
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