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