pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Iklódi Lajos <il@mithrandir.hu>
To: pgsql-sql@postgresql.org
Subject: Execution time problem
Date: Tue, 18 Dec 2001 07:03:32 +0100
Message-ID: <3C1EDC34.BA10E60F@mithrandir.hu> (raw)

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





view thread (3+ messages)  latest in thread

Message-ID: <3C1EDC34.BA10E60F@mithrandir.hu>
Permalink:  ../3C1EDC34.BA10E60F@mithrandir.hu/
Also on:    postgresql.org/message-id/3C1EDC34.BA10E60F@mithrandir.hu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: il@mithrandir.hu
  Subject: Re: Execution time problem
  In-Reply-To: <3C1EDC34.BA10E60F@mithrandir.hu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox