|
| From: | Syan Tan |
| Subject: | [Gnumed-devel] enable_nestedloop should be on |
| Date: | Sat, 18 Mar 2006 11:09:13 +0800 |
for some reason , I thought a nested loop would be slower but it's faster than a hash join.
need to think about it. the query is that explicit child table joining one ( not the nice looking select v_pat_items)
I still can't get the query planner to do index_scan in v_pat_items when searching on pk_health_issue.
I think it is an inheritance join issue.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=100017713.82..100017713.91 rows=19 width=4) (actual time=2931.092..2931.275 rows=15 loops=1)
-> Sort (cost=100017713.82..100017713.86 rows=19 width=4) (actual time=2931.084..2931.149 rows=20 loops=1)
Sort Key: fk_encounter
-> Append (cost=3.08..17713.41 rows=19 width=4) (actual time=0.668..2930.973 rows=20 loops=1)
-> Hash Join (cost=3.08..17621.27 rows=10 width=4) (actual time=0.662..2929.500 rows=20 loops=1)
Hash Cond: ("outer".fk_episode = "inner".pk)
-> Seq Scan on clin_narrative cn (cost=0.00..15787.06 rows=366206 width=8) (actual time=0.074..1625.664 rows=366206 loops=1)
-> Hash (cost=3.07..3.07 rows=5 width=4) (actual time=0.164..0.164 rows=15 loops=1)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (actual time=0.032..0.099 rows=15 loops=1)
Index Cond: (fk_health_issue = 524)
-> Hash Join (cost=1.01..4.12 rows=1 width=4) (actual time=0.407..0.407 rows=0 loops=1)
Hash Cond: ("outer".pk = "inner".fk_episode)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (actual time=0.173..0.235 rows=15 loops=1)
Index Cond: (fk_health_issue = 524)
-> Hash (cost=1.01..1.01 rows=1 width=8) (actual time=0.063..0.063 rows=1 loops=1)
-> Seq Scan on clin_hx_family cn (cost=0.00..1.01 rows=1 width=8) (actual time=0.043..0.047 rows=1 loops=1)
-> Hash Join (cost=3.08..19.09 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)
Hash Cond: ("outer".fk_episode = "inner".pk)
-> Seq Scan on clin_aux_note cn (cost=0.00..14.00 rows=400 width=8) (actual time=0.004..0.004 rows=0 loops=1)
-> Hash (cost=3.07..3.07 rows=5 width=4) (never executed)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (never executed)
Index Cond: (fk_health_issue = 524)
-> Hash Join (cost=1.05..4.19 rows=1 width=4) (actual time=0.220..0.220 rows=0 loops=1)
Hash Cond: ("outer".pk = "inner".fk_episode)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (actual time=0.013..0.072 rows=15 loops=1)
Index Cond: (fk_health_issue = 524)
-> Hash (cost=1.04..1.04 rows=4 width=8) (actual time=0.076..0.076 rows=4 loops=1)
-> Seq Scan on test_result cn (cost=0.00..1.04 rows=4 width=8) (actual time=0.037..0.053 rows=4 loops=1)
-> Hash Join (cost=1.01..4.12 rows=1 width=4) (actual time=0.196..0.196 rows=0 loops=1)
Hash Cond: ("outer".pk = "inner".fk_episode)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (actual time=0.011..0.072 rows=15 loops=1)
Index Cond: (fk_health_issue = 524)
-> Hash (cost=1.01..1.01 rows=1 width=8) (actual time=0.053..0.053 rows=1 loops=1)
-> Seq Scan on allergy cn (cost=0.00..1.01 rows=1 width=8) (actual time=0.038..0.042 rows=1 loops=1)
-> Hash Join (cost=1.02..4.13 rows=1 width=4) (actual time=0.202..0.202 rows=0 loops=1)
Hash Cond: ("outer".pk = "inner".fk_episode)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (actual time=0.011..0.070 rows=15 loops=1)
Index Cond: (fk_health_issue = 524)
-> Hash (cost=1.02..1.02 rows=2 width=8) (actual time=0.060..0.060 rows=2 loops=1)
-> Seq Scan on lab_request cn (cost=0.00..1.02 rows=2 width=8) (actual time=0.036..0.044 rows=2 loops=1)
-> Hash Join (cost=3.08..18.19 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)
Hash Cond: ("outer".fk_episode = "inner".pk)
-> Seq Scan on form_instances cn (cost=0.00..13.40 rows=340 width=8) (actual time=0.004..0.004 rows=0 loops=1)
-> Hash (cost=3.07..3.07 rows=5 width=4) (never executed)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (never executed)
Index Cond: (fk_health_issue = 524)
-> Hash Join (cost=3.08..15.19 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)
Hash Cond: ("outer".fk_episode = "inner".pk)
-> Seq Scan on clin_medication cn (cost=0.00..11.40 rows=140 width=8) (actual time=0.004..0.004 rows=0 loops=1)
-> Hash (cost=3.07..3.07 rows=5 width=4) (never executed)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (never executed)
Index Cond: (fk_health_issue = 524)
-> Hash Join (cost=3.08..18.79 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)
Hash Cond: ("outer".fk_episode = "inner".pk)
-> Seq Scan on referral cn (cost=0.00..13.80 rows=380 width=8) (actual time=0.004..0.004 rows=0 loops=1)
-> Hash (cost=3.07..3.07 rows=5 width=4) (never executed)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (never executed)
Index Cond: (fk_health_issue = 524)
-> Hash Join (cost=1.01..4.12 rows=1 width=4) (actual time=0.239..0.239 rows=0 loops=1)
Hash Cond: ("outer".pk = "inner".fk_episode)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (actual time=0.011..0.121 rows=15 loops=1)
Index Cond: (fk_health_issue = 524)
-> Hash (cost=1.01..1.01 rows=1 width=8) (actual time=0.049..0.049 rows=1 loops=1)
-> Seq Scan on vaccination cn (cost=0.00..1.01 rows=1 width=8) (actual time=0.032..0.036 rows=1 loops=1)
Total runtime: 2931.800 ms
(65 rows)
this is with set enable_nestedloop to on ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=100000111.27..100000111.37 rows=19 width=4) (actual time=1.418..1.629 rows=15 loops=1)
-> Sort (cost=100000111.27..100000111.32 rows=19 width=4) (actual time=1.410..1.491 rows=20 loops=1)
Sort Key: fk_encounter
-> Append (cost=0.00..110.87 rows=19 width=4) (actual time=0.073..1.286 rows=20 loops=1)
-> Nested Loop (cost=0.00..18.95 rows=10 width=4) (actual time=0.066..0.603 rows=20 loops=1)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (actual time=0.032..0.095 rows=15 loops=1)
Index Cond: (fk_health_issue = 524)
-> Index Scan using idx_clnarr_episode on clin_narrative cn (cost=0.00..3.15 rows=2 width=8) (actual time=0.011..0.017 rows=1 loops=15)
Index Cond: (cn.fk_episode = "outer".pk)
-> Nested Loop (cost=0.00..4.04 rows=1 width=4) (actual time=0.047..0.047 rows=0 loops=1)
-> Seq Scan on clin_hx_family cn (cost=0.00..1.01 rows=1 width=8) (actual time=0.007..0.011 rows=1 loops=1)
-> Index Scan using episode_pkey on episode ep (cost=0.00..3.02 rows=1 width=4) (actual time=0.021..0.021 rows=0 loops=1)
Index Cond: ("outer".fk_episode = ep.pk)
Filter: (fk_health_issue = 524)
-> Hash Join (cost=3.08..19.09 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=1)
Hash Cond: ("outer".fk_episode = "inner".pk)
-> Seq Scan on clin_aux_note cn (cost=0.00..14.00 rows=400 width=8) (actual time=0.003..0.003 rows=0 loops=1)
-> Hash (cost=3.07..3.07 rows=5 width=4) (never executed)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (never executed)
Index Cond: (fk_health_issue = 524)
-> Hash Join (cost=1.05..4.19 rows=1 width=4) (actual time=0.190..0.190 rows=0 loops=1)
Hash Cond: ("outer".pk = "inner".fk_episode)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (actual time=0.011..0.071 rows=15 loops=1)
Index Cond: (fk_health_issue = 524)
-> Hash (cost=1.04..1.04 rows=4 width=8) (actual time=0.046..0.046 rows=4 loops=1)
-> Seq Scan on test_result cn (cost=0.00..1.04 rows=4 width=8) (actual time=0.006..0.022 rows=4 loops=1)
-> Nested Loop (cost=0.00..4.04 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=1)
-> Seq Scan on allergy cn (cost=0.00..1.01 rows=1 width=8) (actual time=0.006..0.010 rows=1 loops=1)
-> Index Scan using episode_pkey on episode ep (cost=0.00..3.02 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: ("outer".fk_episode = ep.pk)
Filter: (fk_health_issue = 524)
-> Hash Join (cost=1.02..4.13 rows=1 width=4) (actual time=0.167..0.167 rows=0 loops=1)
Hash Cond: ("outer".pk = "inner".fk_episode)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (actual time=0.010..0.069 rows=15 loops=1)
Index Cond: (fk_health_issue = 524)
-> Hash (cost=1.02..1.02 rows=2 width=8) (actual time=0.029..0.029 rows=2 loops=1)
-> Seq Scan on lab_request cn (cost=0.00..1.02 rows=2 width=8) (actual time=0.006..0.014 rows=2 loops=1)
-> Hash Join (cost=3.08..18.19 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
Hash Cond: ("outer".fk_episode = "inner".pk)
-> Seq Scan on form_instances cn (cost=0.00..13.40 rows=340 width=8) (actual time=0.003..0.003 rows=0 loops=1)
-> Hash (cost=3.07..3.07 rows=5 width=4) (never executed)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (never executed)
Index Cond: (fk_health_issue = 524)
-> Hash Join (cost=3.08..15.19 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
Hash Cond: ("outer".fk_episode = "inner".pk)
-> Seq Scan on clin_medication cn (cost=0.00..11.40 rows=140 width=8) (actual time=0.004..0.004 rows=0 loops=1)
-> Hash (cost=3.07..3.07 rows=5 width=4) (never executed)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (never executed)
Index Cond: (fk_health_issue = 524)
-> Hash Join (cost=3.08..18.79 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=1)
Hash Cond: ("outer".fk_episode = "inner".pk)
-> Seq Scan on referral cn (cost=0.00..13.80 rows=380 width=8) (actual time=0.003..0.003 rows=0 loops=1)
-> Hash (cost=3.07..3.07 rows=5 width=4) (never executed)
-> Index Scan using idx_episode_issue on episode ep (cost=0.00..3.07 rows=5 width=4) (never executed)
Index Cond: (fk_health_issue = 524)
-> Nested Loop (cost=0.00..4.04 rows=1 width=4) (actual time=0.035..0.035 rows=0 loops=1)
-> Seq Scan on vaccination cn (cost=0.00..1.01 rows=1 width=8) (actual time=0.005..0.009 rows=1 loops=1)
-> Index Scan using episode_pkey on episode ep (cost=0.00..3.02 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ("outer".fk_episode = ep.pk)
Filter: (fk_health_issue = 524)
Total runtime: 2.184 ms
(61 rows)
| [Prev in Thread] | Current Thread | [Next in Thread] |