Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBI6vqm26036 for ; Tue, 18 Dec 2001 01:57:52 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 1199ED5E8; Mon, 17 Dec 2001 22:57:51 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id F35F25BF1; Mon, 17 Dec 2001 22:57:50 -0800 (PST) Date: Mon, 17 Dec 2001 22:57:50 -0800 (PST) From: Stephan Szabo To: =?iso-8859-2?Q?Ikl=F3di?= Lajos Cc: Subject: Re: Execution time problem In-Reply-To: <3C1EDC34.BA10E60F@mithrandir.hu> Message-ID: <20011217225518.J57573-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by postgresql.org id fBI79rn26607 X-Archive-Number: 200112/191 X-Sequence-Number: 6014 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 >