Re: Query does not return rows unless a LIMIT statement is used.

Hi Tom!

    Yes, it is always the same tables in both examples. I have no idea why the
query is this way.


On Wednesday 15 February 2006 12:01, Tom Lane wrote:
> Hmm, there's something pretty odd about your data, it seems like.
> In the planner's preferred hash-join plan, it seems to be necessary
> to scan through 654414 rows of pv1 to find the first 5324 rows that
>
> join to pid:
> >  Limit  (cost=27704.04..28790.59 rows=5324 width=691) (actual
> > time=3865.677..17464.071 rows=5324 loops=1)
> >    ->  Hash Join  (cost=27704.04..264408.14 rows=1159822 width=691)
> > (actual time=3865.674..17453.938 rows=5324 loops=1)
> >          Hash Cond: (("outer".patient_id_internal_id)::text =
> > ("inner".patient_id_internal_id)::text)
> >          ->  Seq Scan on pv1  (cost=0.00..80441.22 rows=1159822
> > width=407) (actual time=4.907..9361.791 rows=654414 loops=1)
>                                ^^^^^^^^^^^
> This is strange because all the other results look like there's about a
> one-to-one correspondence.  In the case that is fast for you, the outer
> scan appears to have matched every time:
>
> Do you have any idea why the hash case is so unlucky?
>
> BTW, these examples sure look like they are not being done on exactly
> the same tables... the names are different.
>
>             regards, tom lane

--
Regards,

Richard Kut
Database Administrator
Research & Development
Intelerad Medical Systems Inc.
460 Ste-Catherine West, Suite 210
Montreal, Quebec, Canada H3B 1A7
Tel:     514.931.6222 x7733
Fax:     514.931.4653
rkut@intelerad.com
www.intelerad.com

This email or any attachments may contain confidential or legally
privileged information intended for the sole use of the addressees. Any
use, redistribution, disclosure, or reproduction of this information,
except as intended, is prohibited. If you received this
email in error, please notify the sender and remove all copies of the
message, including any attachments.