From: Chris Mair <chrisnospam@1006.org>
To: James Im <im-james@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: inner join is much faster! is that right?
Date: Tue, 19 Sep 2006 14:29:37 +0200
Message-ID: <1158668977.15510.19.camel@ultra.home.lan> (raw)
In-Reply-To: <BAY7-F18EF4F5BF559DFDA8D9C9F96220@phx.gbl>
References: <BAY7-F18EF4F5BF559DFDA8D9C9F96220@phx.gbl>
> I really thought that Postgresql would rewrite a query from
>
> select *
> from file a, file_tag b, tag c
> where a.id_file=b.id_file and b.id_tag=c.id_tag and a.id_file=10000001000000
>
> to something like:
>
> select *
> from (file a inner join file_tag b on (a.id_file=b.id_file)) inner join
> tag c on (b.id_tag=c.id_tag)
> where a.id_file=10000001000000
These shouldn't be other than two ways to express the same (inner)
join.
Your timings seem to suggest that in the first case PG computes
the whole join between 3 tables and only then applies the filter
in a.
Can you send the outputs of "explain <query>" for these two and
let us know what version of PG this is?
Bye, Chris.
--
Chris Mair
http://www.1006.org
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-sql@postgresql.org
Cc: chrisnospam@1006.org, im-james@hotmail.com
Subject: Re: inner join is much faster! is that right?
In-Reply-To: <1158668977.15510.19.camel@ultra.home.lan>
* 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