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

Hi Tom!

Here are the results with enable_seqscan = on:

hl7segmentsihe=> set enable_seqscan = on;
SET
Time: 0.210 ms
hl7segmentsihe=> EXPLAIN ANALYZE SELECT *
hl7segmentsihe-> FROM pid, pv1
hl7segmentsihe-> WHERE pid.patient_id_internal_id = pv1.patient_id_internal_id
hl7segmentsihe-> LIMIT 5324;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
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)
-> Hash (cost=14375.03..14375.03 rows=328403 width=284) (actual
time=3853.090..3853.090 rows=328403 loops=1)
-> Seq Scan on pid (cost=0.00..14375.03 rows=328403
width=284) (actual time=6.198..2658.306 rows=328403 loops=1)
Total runtime: 18120.833 ms
(7 rows)

Time: 18148.473 ms
hl7segmentsihe=> EXPLAIN ANALYZE SELECT *
hl7segmentsihe-> FROM pid, pv1
hl7segmentsihe-> WHERE pid.patient_id_internal_id = pv1.patient_id_internal_id
hl7segmentsihe-> ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=27704.04..264408.14 rows=1159822 width=691) (actual
time=3469.373..107465.440 rows=1159805 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.977..10166.541 rows=1159822 loops=1)
-> Hash (cost=14375.03..14375.03 rows=328403 width=284) (actual
time=3457.473..3457.473 rows=328403 loops=1)
-> Seq Scan on pid (cost=0.00..14375.03 rows=328403 width=284)
(actual time=6.322..2288.957 rows=328403 loops=1)
Total runtime: 108304.628 ms
(6 rows)

Time: 108305.898 ms
hl7segmentsihe=>

I hope that this helps.

On Wednesday 15 February 2006 11:21, Tom Lane wrote:
> Richard Kut <rkut(at)intelerad(dot)com> writes:
> > -> Hash (cost=49229.38..49229.38 rows=328403 width=284) (actual
> > time=3342.874..3342.874 rows=328403 loops=1)
> > -> Index Scan using kdx_pid on pid (cost=0.00..49229.38
> > rows=328403 width=284) (actual time=0.010..2110.172 rows=328403 loops=1)
>
> Hm, it looks like you've still got the planner's hands tied behind its
> back --- specifically, I'll bet enable_seqscan = off. There's no reason
> at all to use a full-table indexscan to load a hash table. Please try
> it again with default planner parameter settings.
>
> A general comment from comparing your two cases is that random_page_cost
> is probably too high for the scenario you are testing, which looks to be
> a case where both tables are fully cached in memory. However, before
> reducing it you should ask yourself whether that's still likely to be
> true in production. It's a bad idea to optimize on the basis of test
> cases that are much smaller than your production scenario will be ...
>
> 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(at)intelerad(dot)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.