From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Iklódi Lajos <il@mithrandir.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Execution time problem
Date: Mon, 17 Dec 2001 22:57:50 -0800 (PST)
Message-ID: <20011217225518.J57573-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <3C1EDC34.BA10E60F@mithrandir.hu>
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
>
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: sszabo@megazone23.bigpanda.com, il@mithrandir.hu
Subject: Re: Execution time problem
In-Reply-To: <20011217225518.J57573-100000@megazone23.bigpanda.com>
* 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