Re: Execution time problem - Mailing list pgsql-sql

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