Can anybody help me to resolve a problem?
In the first example, when Postgres uses index, execution time is good.
In the second example, tables are with the same structure, but Postgres
uses seq scan instead of using unique index "rentet_rttsz_key", so
execution time becomes very bad.
EXAMPLE 1
test1=# explain
test1-# select rtcikkod, vttsz
test1-# from rvtet, rentet
test1-# where
test1-# rvtet.vttsz= 13524
test1-# and rvtet.vtrtsz=rentet.rttsz
test1-# ;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..4.16 rows=1 width=16) -> Index Scan using rvtet_tszrtsz on rvtet (cost=0.00..2.05 rows=1
width=8) -> Index Scan using rentet_rttsz_key on rentet (cost=0.00..2.09
rows=1 width=8)
EXPLAIN
test1=# select count(*) from rvtet;count
-------13112
(1 row)
test1=# select count(*) from rentet;count
-------13571
(1 row)
EXAMPLE 2
test2=# explain
test2-# select rtcikkod, vttsz
test2-# from rvtet, rentet
test2-# where
test2-# rvtet.vttsz= 13524
test2-# and rvtet.vtrtsz=rentet.rttsz
test2-# ;
NOTICE: QUERY PLAN:
Merge Join (cost=1726.50..1927.18 rows=24219 width=16) -> Sort (cost=131.77..131.77 rows=152 width=8) ->
IndexScan using rvtet_tszrtsz on rvtet (cost=0.00..126.25
rows=152 width=8) -> Sort (cost=1594.73..1594.73 rows=15902 width=8) -> Seq Scan on rentet (cost=0.00..485.02
rows=15902width=8)
EXPLAIN
test2=# \di rentet List of relations Name | Type | Owner
------------------+-------+-------rentet_rtsz | index | wwwrentet_rttsz_key | index | www
(2 rows)
test2=# select count(*) from rentet;count
-------15902
(1 row)
test2=# select count(*) from rvtet;count
-------15230
(1 row)
Thanks for everyone.
Lajos Iklodi
