Re: Some question - Mailing list pgsql-performance

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.