On Tue, 18 Dec 2001, [iso-8859-2] Ikl�di Lajos wrote:
> 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.
Have you run vacuum analyze? What version(s) are you running.
Given that the second seems to be estimating over 24000 matching
rows and the first 1, that probably has something to do with it.
>
> 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)
> -> Index Scan 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=15902 width=8)
>
> EXPLAIN
> test2=# \di rentet
> List of relations
> Name | Type | Owner
> ------------------+-------+-------
> rentet_rtsz | index | www
> rentet_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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>