Re: Execution time problem

Thanks for the good idea! It really decided my problems. It was on 7.1.3.

Lajos

Stephan Szabo írta:

> 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(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster