Thread: Execution time problem

Execution time problem

From

Iklódi Lajos

Date:

18 December 2001, 04:11:08

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

Re: Execution time problem

From

Stephan Szabo

Date:

18 December 2001, 05:10:02

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
>

Re: Execution time problem

From

Iklódi Lajos

Date:

18 December 2001, 18:40:14

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