pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Iklódi Lajos <il@mithrandir.hu>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Execution time problem
Date: Tue, 18 Dec 2001 21:36:37 +0100
Message-ID: <3C1FA8D5.5258ED51@mithrandir.hu> (raw)
References: <20011217225518.J57573-100000@megazone23.bigpanda.com>

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




view thread (3+ messages)

Message-ID: <3C1FA8D5.5258ED51@mithrandir.hu>
Permalink:  ../3C1FA8D5.5258ED51@mithrandir.hu/
Also on:    postgresql.org/message-id/3C1FA8D5.5258ED51@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, sszabo@megazone23.bigpanda.com
  Subject: Re: Execution time problem
  In-Reply-To: <3C1FA8D5.5258ED51@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