From: Ľubomír Varga <luvar@plaintext.sk>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Some question
Date: Sat, 10 Apr 2010 23:42:21 +0200
Message-ID: <201004102342.21542.luvar@plaintext.sk> (raw)
In-Reply-To: <4BBB6B540200002500030531@gw.wicourts.gov>
References: <201003311746.38192.luvar@plaintext.sk>
<4BBB6B540200002500030531@gw.wicourts.gov>
Hi, here are they:
----------------------------------------------------
select * from t_route_type;
ID;description;type
1;"stojim";0
2;"idem";1
----------------------------------------------------
explain analyze
SELECT * FROM t_route
WHERE t_route.route_type_fk = 1
limit 4;
"Limit (cost=0.00..0.88 rows=4 width=2640) (actual time=23.352..23.360 rows=4
loops=1)"
" -> Seq Scan on t_route (cost=0.00..120497.00 rows=549155 width=2640)
(actual time=23.350..23.354 rows=4 loops=1)"
" Filter: (route_type_fk = 1)"
"Total runtime: 23.404 ms"
----------------------------------------------------
explain analyze
SELECT * FROM t_route
WHERE t_route.route_type_fk = (SELECT id FROM t_route_type WHERE type = 2)
limit 4;
"Limit (cost=1.02..1.91 rows=4 width=2640) (actual
time=267243.019..267243.019 rows=0 loops=1)"
" InitPlan"
" -> Seq Scan on t_route_type (cost=0.00..1.02 rows=1 width=8) (actual
time=0.006..0.006 rows=0 loops=1)"
" Filter: ("type" = 2)"
" -> Seq Scan on t_route (cost=0.00..120498.12 rows=545885 width=2640)
(actual time=267243.017..267243.017 rows=0 loops=1)"
" Filter: (route_type_fk = $0)"
"Total runtime: 267243.089 ms"
----------------------------------------------------
explain analyze
SELECT * FROM t_route, t_route_type
WHERE t_route.route_type_fk = t_route_type.id AND
type = 2
limit 4;
"Limit (cost=0.00..0.96 rows=4 width=2661) (actual time=0.013..0.013 rows=0
loops=1)"
" -> Nested Loop (cost=0.00..131415.62 rows=545880 width=2661) (actual
time=0.012..0.012 rows=0 loops=1)"
" Join Filter: (t_route.route_type_fk = t_route_type.id)"
" -> Seq Scan on t_route_type (cost=0.00..1.02 rows=1 width=21)
(actual time=0.011..0.011 rows=0 loops=1)"
" Filter: ("type" = 2)"
" -> Seq Scan on t_route (cost=0.00..117767.60 rows=1091760
width=2640) (never executed)"
"Total runtime: 0.054 ms"
So I found solution. It is third select, where is used join instead of inner
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
columns and one geometry column with line of travelled path. Type of route is
in t_route_type and it could be "travelling" and "standing" type. In my
select I want to select some routes which are type "travelling" (type = 1, id
= 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ír 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
--
Odborník na všetko je zlý odborník. Ja sa snažím byť výnimkou potvrdzujúcou
pravidlo.
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-performance@postgresql.org
Cc: luvar@plaintext.sk, Kevin.Grittner@wicourts.gov
Subject: Re: Some question
In-Reply-To: <201004102342.21542.luvar@plaintext.sk>
* 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