From: James Im <im-james@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: inner join is much faster! is that right?
Date: Tue, 19 Sep 2006 09:21:22 +0000
Message-ID: <BAY7-F18EF4F5BF559DFDA8D9C9F96220@phx.gbl> (raw)
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
However this is not the case!
I have a many-to-many relation between 2 tables (tag and file). tag has
40000 rows and file has 5 millions rows. file_tag has 50 millions rows.
In this setup, the first select takes a couple of minutes to complete
while the second query takes only a second to complete.
Is this a normal expected behavior? Shouldn't Postgresql be able to
rewrite the query correctly?
_________________________________________________________________
Få 250 MB gratis lagerplads på MSN Hotmail: http://www.hotmail.com
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: im-james@hotmail.com
Subject: Re: inner join is much faster! is that right?
In-Reply-To: <BAY7-F18EF4F5BF559DFDA8D9C9F96220@phx.gbl>
* 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