From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Peter Galbavy <peter(dot)galbavy(at)knowtion(dot)net> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: 'fake' join and performance ? |
Date: | 2002-10-22 17:31:35 |
Message-ID: | 20021022102934.U88979-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Tue, 22 Oct 2002, Peter Galbavy wrote:
> OK, I am now confused; postgresql 7.3beta2 on OpenBSD:
>
>
> photos=# select * from metadata WHERE name = 'Make' and value = 'Canon'
> limit 10;
>
> *bang*, 10 values, sub second response.
>
> photos=# select * from metadata m, images i WHERE m.name = 'Make' and
> m.value = 'Canon' limit 10;
>
> *yawn* - see you later...
>
> ----------------------------------------------------------------------------
> ------------------------
> Limit (cost=0.00..27712.04 rows=6 width=816)
> -> Nested Loop (cost=0.00..31073.00 rows=7 width=816)
> -> Index Scan using metadata_index_2 on metadata m
> (cost=0.00..31072.94 rows=7 width=92)
> Index Cond: (name = 'Make'::text)
> Filter: (value = 'Canon'::text)
> -> Seq Scan on images i (cost=0.00..0.00 rows=1 width=724)
> (6 rows)
>
>
>
> Er, what's that nested loop. I *know* I have shot myself in the foot
> somehow, but my initial reaction was that the optimiser should just make the
> 'fake' (i.e. unreferenced) reference to another table go away...
It can't do that. The second query would give multiple copies of each row
in metadata for each row in images. I'm surprised that it'd be so slow if
images is completely empty though. What does explain analyze show for the
real times.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-10-22 17:39:25 | Re: 'fake' join and performance ? |
Previous Message | Josh Berkus | 2002-10-22 16:16:09 | Re: Can I search for an array in csf? |