От: Ľubomír Varga
Тема: Re: Some question
Дата: ,
Msg-id: 201004102342.21542.luvar@plaintext.sk
(см: обсуждение, исходный текст)
Ответ на: Re: Some question  ("Kevin Grittner")
Список: pgsql-performance

Скрыть дерево обсуждения

Some question  (Ľubomír Varga, )
 Re: Some question  (Scott Marlowe, )
  Re: Some question  (Yeb Havinga, )
 Re: Some question  ("Kevin Grittner", )
  Re: Some question  (Ľubomír Varga, )
 Re: Some question  ("Kevin Grittner", )

Hi, here are they:

select * from t_route_type;


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
"  ->  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
"  ->  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<> 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

В списке pgsql-performance по дате сообщения:

От: Bruce Momjian
Сообщение: Re: *** PROBABLY SPAM *** Does the psql executable support a "fetch many" approach when dumping large queries to stdout?
От: norn
Сообщение: Re: significant slow down with various LIMIT