Message from Corin at 03-19-2010 01:26:35 PM
--
***snip
The intention of the query is to find rows with no "partner" row. The
offset and limit are just to ignore the time needed to send the result
to the client.
---
I don't understand the point of OFFSET, limit will accomplish
Matthew Wakeling writes:
> On Fri, 19 Mar 2010, Stephen Frost wrote:
>> ...it has to go to an external on-disk sort (see later on, and how to
>> fix that).
> This was covered on this list a few months ago, in
> http://archives.postgresql.org/pgsql-performance/2009-08/msg00184.php and
> http://
On Fri, 19 Mar 2010, Stephen Frost wrote:
...it has to go to an external on-disk sort (see later on, and how to
fix that).
This was covered on this list a few months ago, in
http://archives.postgresql.org/pgsql-performance/2009-08/msg00184.php and
http://archives.postgresql.org/pgsql-performa
K.I.S.S. here . the best way to do one of these in most DB's is
typically an outer join and test for null:
select f1.* from friends f1
left outer join friends f2 on (f1.user_id=f2.ref_id and
f1.ref_id=f2.user_id)
where f2.id is null;
On Fri, Mar 19, 2010 at 7:26 AM, Corin wrote:
> Hi
Corin,
* Corin (wakath...@gmail.com) wrote:
> I fill this table with around 2.800.000 random rows (values between 1
> and 500.000 for user_id, ref_id).
Using random data really isn't a good test.
> The intention of the query is to find rows with no "partner" row. The
> offset and limit are j
Corin wrote:
> It's already faster, which is great, but I wonder why the query
> plan is that complex.
Because that's the plan, out of all the ways the planner knows to
get the requested result set, which was estimated to cost the least.
If it isn't actually the fastest, that might suggest tha
Hi all!
While evaluting the pgsql query planer I found some weird behavior of
the query planer. I think it's plan is way too complex and could much
faster?
CREATE TABLE friends (
id integer NOT NULL,
user_id integer NOT NULL,
ref_id integer NOT NULL,
);
ALTER TABLE ONLY friends ADD
