Execution time problem

From: Iklódi Lajos <il(at)mithrandir(dot)hu>
To: pgsql-sql(at)postgresql(dot)org
Subject: Execution time problem
Date: 2001-12-18 06:03:32
Message-ID: 3C1EDC34.BA10E60F@mithrandir.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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)
-> 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2001-12-18 06:57:50 Re: Execution time problem
Previous Message S P Arif Sahari Wibowo 2001-12-18 05:33:43 Operation on bit strings with different length