From: | "James Im" <im-james(at)hotmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | inner join is much faster! is that right? |
Date: | 2006-09-19 09:21:22 |
Message-ID: | BAY7-F18EF4F5BF559DFDA8D9C9F96220@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ezequias Rodrigues da Rocha | 2006-09-19 12:11:51 | Borland Database Engine assumes Varchar(255) as Memo |
Previous Message | Aaron Bono | 2006-09-17 16:16:03 | Re: Dividing results from two tables with different time frames |