From: =?utf-8?q?=C4=BDubom=C3=ADr_Varga?= <luvar@plaintext.sk>
Reply-To: luvar@plaintext.sk
Organization: LuVar
To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Subject: Re: Some question
Date: Sat, 10 Apr 2010 23:42:21 +0200
User-Agent: KMail/1.9.10
Cc: pgsql-performance@postgresql.org
References: <201003311746.38192.luvar@plaintext.sk>
	<4BBB6B540200002500030531@gw.wicourts.gov>
In-Reply-To: <4BBB6B540200002500030531@gw.wicourts.gov>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201004102342.21542.luvar@plaintext.sk>

Hi, here are they:

=2D---------------------------------------------------
select * from t_route_type;

ID;description;type
1;"stojim";0
2;"idem";1


=2D---------------------------------------------------
explain analyze
SELECT * FROM t_route
	WHERE t_route.route_type_fk =3D 1
	limit 4;

"Limit  (cost=3D0.00..0.88 rows=3D4 width=3D2640) (actual time=3D23.352..23=
=2E360 rows=3D4=20
loops=3D1)"
"  ->  Seq Scan on t_route  (cost=3D0.00..120497.00 rows=3D549155 width=3D2=
640)=20
(actual time=3D23.350..23.354 rows=3D4 loops=3D1)"
"        Filter: (route_type_fk =3D 1)"
"Total runtime: 23.404 ms"


=2D---------------------------------------------------
explain analyze
SELECT * FROM t_route
	WHERE t_route.route_type_fk =3D (SELECT id FROM t_route_type WHERE type =
=3D 2)
	limit 4;

"Limit  (cost=3D1.02..1.91 rows=3D4 width=3D2640) (actual=20
time=3D267243.019..267243.019 rows=3D0 loops=3D1)"
"  InitPlan"
"    ->  Seq Scan on t_route_type  (cost=3D0.00..1.02 rows=3D1 width=3D8) (=
actual=20
time=3D0.006..0.006 rows=3D0 loops=3D1)"
"          Filter: ("type" =3D 2)"
"  ->  Seq Scan on t_route  (cost=3D0.00..120498.12 rows=3D545885 width=3D2=
640)=20
(actual time=3D267243.017..267243.017 rows=3D0 loops=3D1)"
"        Filter: (route_type_fk =3D $0)"
"Total runtime: 267243.089 ms"


=2D---------------------------------------------------
explain analyze
SELECT * FROM t_route, t_route_type
	WHERE t_route.route_type_fk =3D t_route_type.id AND
		type =3D 2
	limit 4;

"Limit  (cost=3D0.00..0.96 rows=3D4 width=3D2661) (actual time=3D0.013..0.0=
13 rows=3D0=20
loops=3D1)"
"  ->  Nested Loop  (cost=3D0.00..131415.62 rows=3D545880 width=3D2661) (ac=
tual=20
time=3D0.012..0.012 rows=3D0 loops=3D1)"
"        Join Filter: (t_route.route_type_fk =3D t_route_type.id)"
"        ->  Seq Scan on t_route_type  (cost=3D0.00..1.02 rows=3D1 width=3D=
21)=20
(actual time=3D0.011..0.011 rows=3D0 loops=3D1)"
"              Filter: ("type" =3D 2)"
"        ->  Seq Scan on t_route  (cost=3D0.00..117767.60 rows=3D1091760=20
width=3D2640) (never executed)"
"Total runtime: 0.054 ms"


So I found solution. It is third select, where is used join instead of inne=
r=20
select to get ID for some constant from t_route_type.

t_route is table with routes taken by some car. It have same strings as=20
columns and one geometry column with line of travelled path. Type of route =
is=20
in t_route_type and it could be "travelling" and "standing" type. In my=20
select I want to select some routes which are type "travelling" (type =3D 1=
, id=20
=3D 2). It is only sample select.

Please explain me why second query had taken so long to finish.

Have a nice day.


On Wednesday 07 April 2010 00:11:48 Kevin Grittner wrote:
> *ubom=C3=ADr Varga<luvar@plaintext.sk> wrote:
> > Hi, stright to my "problem":
>
> Please show the exact problem query and the results of running it
> with EXPLAIN ANALYZE, along with the other information suggested
> here:
>
> http://wiki.postgresql.org/wiki/SlowQueryQuestions
>
> -Kevin

=2D-=20
Odborn=C3=ADk na v=C5=A1etko je zl=C3=BD odborn=C3=ADk. Ja sa sna=C5=BE=C3=
=ADm by=C5=A5 v=C3=BDnimkou potvrdzuj=C3=BAcou=20
pravidlo.