pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinner join is much faster! is that right?
3+ messages / 2 participants
[nested] [flat]
* inner join is much faster! is that right?
@ 2006-09-19 09:21 James Im <im-james@hotmail.com>
2006-09-19 12:29 ` Re: inner join is much faster! is that right? Chris Mair <chrisnospam@1006.org>
0 siblings, 1 reply; 3+ messages in thread
From: James Im @ 2006-09-19 09:21 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: inner join is much faster! is that right?
2006-09-19 09:21 inner join is much faster! is that right? James Im <im-james@hotmail.com>
@ 2006-09-19 12:29 ` Chris Mair <chrisnospam@1006.org>
0 siblings, 0 replies; 3+ messages in thread
From: Chris Mair @ 2006-09-19 12:29 UTC (permalink / raw)
To: James Im <im-james@hotmail.com>; +Cc: 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
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: inner join is much faster! is that right?
@ 2006-09-19 13:44 James Im <im-james@hotmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: James Im @ 2006-09-19 13:44 UTC (permalink / raw)
To: pgsql-sql
I was doing the explain thing when I discovered my mistake!
The think is that I over simplyfied my examples. instead of searching
for one ID, I was searching for several ids and I forgot to put a
parentesis arround the or like this.
I was doing this:
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 or a.id_file=10000001000200
But I should have done this:
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 or a.id_file=10000001000200)
This changes everything. The performance is now acceptable!
_________________________________________________________________
Vælg selv hvordan du vil kommunikere - skrift, tale, video eller billeder
med MSN Messenger: http://messenger.msn.dk/ - her kan du det hele
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-09-19 13:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-19 09:21 inner join is much faster! is that right? James Im <im-james@hotmail.com>
2006-09-19 12:29 ` Chris Mair <chrisnospam@1006.org>
2006-09-19 13:44 Re: inner join is much faster! is that right? James Im <im-james@hotmail.com>
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