select * from udr.vhastakayit limit 100

Merhaba.
Aşağıdaki bir view var.

CREATE OR REPLACE VIEW udr.vhastakayit AS
SELECT hastakayit AS hastakayit, hastakayit.odano,
hastakayit.taburcutarihi, hastakayit.medulaepikrizuzunlugu,
hastakayit.faturagondersonuckodu, hastakayit.epikriz,
hastakayit.epikrizuzunlugu, hastakayit.medulatoplamtutar,
hastakayit.medulatutar, hastakayit.protokolno, hastakayit.hastano,
kimlik.hastaadi, kimlik.telcep, kimlik.telev, kimlik.dogumtarihi,
kimlik.babaadi, kimlik.tckimlikno, hastakayit.devredilenkurum,
hastakayit.gelistarihsaati, hastakayit.fiyattarifesi,
hastakayit.takipno, hastakayit.gelistarihi, hastakayit.tedavituru,
hastakayit.yas, hastakayit.odemegondersonuckodu,
hastakayit.hastabasvuruno, hastakayit.provizyontarihi,
hastakayit.geldigibolum, hastakayit.faturaonayi,
hastakayit.faturatescilno, hastakayit.donemsonlandirmano,
hastakayit.taburcuonayi, hastakayit.taburcuedildi,
hastakayit.medulafaturatarihi, hastakayit.gecicitakip,
hastakayit.anakayit, hastakayit.akbs, hastakayit.epikriztutarsizliklari,
uzmanlikdali.bransadi, provizyontipi.adi AS provizyontipi, takiptipi.adi
AS takiptipi, tedavituru.adi AS tedavituruadi, taburcukodu.adi AS
taburcukodu, kimlik.karneno, kimlik.sigortalituru, kimlik.sicilno,
kimlik.yakinlikkodu, kimlik.cinsiyet
FROM udr.hastakayit
LEFT JOIN udr.kimlik ON kimlik.hastano::text = hastakayit.hastano::text
LEFT JOIN udr.uzmanlikdali ON uzmanlikdali.branskodu::text =
hastakayit.geldigibolum::text
LEFT JOIN udr.provizyontipi ON provizyontipi.kodu::text =
hastakayit.provizyontipi::text
LEFT JOIN udr.takiptipi ON takiptipi.kodu::text =
hastakayit.takiptipi::text
LEFT JOIN udr.tedavituru ON tedavituru.kodu::text =
hastakayit.tedavituru::text
LEFT JOIN udr.taburcukodu ON taburcukodu.kodu::text =
hastakayit.taburcukodu::text;

Pgadmin de "select * from udr.vhastakayit limit 100" ile kayıtlar 8500
ms de gelirken
"select * from udr.vhastakayit limit 1000" ile kayıtlar 4500 ms de
geliyor. Bu çelişki nereden kaynaklanabilir?
İyi çalışmalar.

-- Executing query:
select * from udr.vhastakayit limit 1000
Total query runtime: 4571 ms.
1000 rows retrieved.

-- Executing query:
select * from udr.vhastakayit limit 100
Total query runtime: 9251 ms.
100 rows retrieved.

*EXPLAIN ANALYZE select * from udr.vhastakayit limit 100*
"Limit (cost=0.00..24445.87 rows=100 width=903) (actual
time=30.029..11702.456 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=0.00..4482430977.39 rows=18336150
width=903) (actual time=30.027..11702.202 rows=100 loops=1)"
" Join Filter: ((kimlik.hastano)::text = (hastakayit.hastano)::text)"
" -> Nested Loop Left Join (cost=0.00..72705.64 rows=49100
width=821) (actual time=0.062..6.837 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=0.00..58941.71
rows=49100 width=802) (actual time=0.052..5.990 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=0.00..45177.78
rows=49100 width=685) (actual time=0.046..5.292 rows=100 loops=1)"
" -> Nested Loop Left Join
(cost=0.00..31413.86 rows=49100 width=468) (actual time=0.038..4.554
rows=100 loops=1)"
" -> Nested Loop Left Join
(cost=0.00..17649.93 rows=49100 width=390) (actual time=0.032..3.224
rows=100 loops=1)"
" -> Seq Scan on hastakayit
(cost=0.00..3886.00 rows=49100 width=274) (actual time=0.014..0.884
rows=100 loops=1)"
" -> Index Scan using
taburcukodu_kodu_pk on taburcukodu (cost=0.00..0.27 rows=1 width=130)
(actual time=0.014..0.016 rows=1 loops=100)"
" Index Cond:
((taburcukodu.kodu)::text = (hastakayit.taburcukodu)::text)"
" -> Index Scan using tedavi_turu_pk on
tedavituru (cost=0.00..0.27 rows=1 width=102) (actual time=0.005..0.008
rows=1 loops=100)"
" Index Cond:
((tedavituru.kodu)::text = (hastakayit.tedavituru)::text)"
" -> Index Scan using takip_tipi_pk on
takiptipi (cost=0.00..0.27 rows=1 width=242) (actual time=0.002..0.003
rows=0 loops=100)"
" Index Cond: ((takiptipi.kodu)::text =
(hastakayit.takiptipi)::text)"
" -> Index Scan using provizyon_tipi_pk on
provizyontipi (cost=0.00..0.27 rows=1 width=142) (actual
time=0.002..0.003 rows=0 loops=100)"
" Index Cond: ((provizyontipi.kodu)::text =
(hastakayit.provizyontipi)::text)"
" -> Index Scan using uzmanlikdali_index on uzmanlikdali
(cost=0.00..0.27 rows=1 width=24) (actual time=0.003..0.004 rows=0
loops=100)"
" Index Cond: ((uzmanlikdali.branskodu)::text =
(hastakayit.geldigibolum)::text)"
" -> Seq Scan on kimlik (cost=0.00..89609.89 rows=74689
width=86) (actual time=0.005..87.730 rows=24780 loops=100)"
"Total runtime: 11702.816 ms"

*EXPLAIN ANALYZE select * from udr.vhastakayit limit 1000*
"Limit (cost=90551.14..90686.48 rows=1000 width=903) (actual
time=131.215..145.557 rows=1000 loops=1)"
" -> Hash Left Join (cost=90551.14..2572302.34 rows=18336150
width=903) (actual time=131.213..144.374 rows=1000 loops=1)"
" Hash Cond: ((hastakayit.hastano)::text = (kimlik.hastano)::text)"
" -> Hash Left Join (cost=7.63..6225.22 rows=49100 width=821)
(actual time=0.261..10.146 rows=1000 loops=1)"
" Hash Cond: ((hastakayit.geldigibolum)::text =
(uzmanlikdali.branskodu)::text)"
" -> Hash Left Join (cost=4.38..5734.75 rows=49100
width=802) (actual time=0.100..8.479 rows=1000 loops=1)"
" Hash Cond: ((hastakayit.tedavituru)::text =
(tedavituru.kodu)::text)"
" -> Hash Left Join (cost=3.31..5181.31 rows=49100
width=724) (actual time=0.086..6.894 rows=1000 loops=1)"
" Hash Cond: ((hastakayit.takiptipi)::text =
(takiptipi.kodu)::text)"
" -> Hash Left Join (cost=2.16..4687.58
rows=49100 width=507) (actual time=0.066..5.411 rows=1000 loops=1)"
" Hash Cond:
((hastakayit.provizyontipi)::text = (provizyontipi.kodu)::text)"
" -> Hash Left Join (cost=1.02..4193.90
rows=49100 width=390) (actual time=0.048..3.946 rows=1000 loops=1)"
" Hash Cond:
((hastakayit.taburcukodu)::text = (taburcukodu.kodu)::text)"
" -> Seq Scan on hastakayit
(cost=0.00..3886.00 rows=49100 width=274) (actual time=0.014..2.335
rows=1000 loops=1)"
" -> Hash (cost=1.01..1.01 rows=1
width=130) (actual time=0.024..0.024 rows=11 loops=1)"
" -> Seq Scan on
taburcukodu (cost=0.00..1.01 rows=1 width=130) (actual
time=0.004..0.011 rows=11 loops=1)"
" -> Hash (cost=1.06..1.06 rows=6
width=142) (actual time=0.013..0.013 rows=6 loops=1)"
" -> Seq Scan on provizyontipi
(cost=0.00..1.06 rows=6 width=142) (actual time=0.002..0.006 rows=6
loops=1)"
" -> Hash (cost=1.07..1.07 rows=7 width=242)
(actual time=0.014..0.014 rows=7 loops=1)"
" -> Seq Scan on takiptipi
(cost=0.00..1.07 rows=7 width=242) (actual time=0.002..0.007 rows=7
loops=1)"
" -> Hash (cost=1.03..1.03 rows=3 width=102)
(actual time=0.008..0.008 rows=3 loops=1)"
" -> Seq Scan on tedavituru (cost=0.00..1.03
rows=3 width=102) (actual time=0.003..0.006 rows=3 loops=1)"
" -> Hash (cost=2.00..2.00 rows=100 width=24) (actual
time=0.155..0.155 rows=100 loops=1)"
" -> Seq Scan on uzmanlikdali (cost=0.00..2.00
rows=100 width=24) (actual time=0.006..0.071 rows=100 loops=1)"
" -> Hash (cost=89609.89..89609.89 rows=74689 width=86) (actual
time=130.914..130.914 rows=24883 loops=1)"
" -> Seq Scan on kimlik (cost=0.00..89609.89 rows=74689
width=86) (actual time=0.007..92.707 rows=24883 loops=1)"
"Total runtime: 146.363 ms"

--
Namık Güngör
Ofis İletişim Bilgisayar San.Tic.Ltd.Şti.
İzzetpaşa Mah. Yeniyol Cd. No:4/2
34387 Şişli - İstanbul
Tel : +90 212 219 88 77
Faks : +90 212 219 88 80

namik(at)ofisiletisim(dot)com
www.ofisiletisim.com