Skip site navigation (1) Skip section navigation (2)

Re: SELECT ... WHERE ... IN (SELECT ...) ->

From: "Alexander M(dot) Pravking" <fduch(at)antar(dot)bryansk(dot)ru>
To: Anton <anton200(at)gmail(dot)com>
Cc: Sergey Suleymanov <solt(at)eatpbank(dot)ru>, pgsql-ru-general(at)postgresql(dot)org
Subject: Re: SELECT ... WHERE ... IN (SELECT ...) ->
Date: 2006-12-05 07:37:41
Message-ID: 20061205073741.GB12175@dyatel.antar.bryansk.ru (view raw or flat)
Thread:
Lists: pgsql-ru-general
On Tue, 2006-12-05 at 12:01 +0500, Anton wrote:
> >        А если таки явно нарисовать join ?
> то ничего не меняется, т.к. оно как раз это вроде бы само-то и делает.
> 
> =# explain analyze
> billing-#   SELECT n_traffic.collect_time
> billing-#   FROM n_logins
> billing-#        JOIN n_traffic ON (n_traffic.login_id = n_logins.login_id 
> AND
> billing(#                           n_traffic.collect_time >
> '1970-01-01 00:00:00')
> billing-#   WHERE n_logins.account_id = '1655'
> billing-#   ORDER BY n_traffic.collect_time, n_traffic.login_id LIMIT 1;
> ---------------------------------------
> Limit  (cost=0.00..2029.81 rows=1 width=12) (actual
> time=5473.602..5473.602 rows=0 loops=1)
>   ->  Nested Loop  (cost=0.00..876879.12 rows=432 width=12) (actual
> time=5473.595..5473.595 rows=0 loops=1)
>         ->  Index Scan using n_traffic_collect_time_login_id on
> n_traffic  (cost=0.00..11094.75 rows=273472 width=12) (actual
> time=0.036..1130.476 rows=273486 loops=1)
>               Index Cond: (collect_time > '1970-01-01
> 00:00:00'::timestamp without time zone)

IMHO, этот индекс вообще лишний. Можно попробовать снести, и у
оптимизатора не останется ничего другого, кроме как использовать индекс
по login_id.

>         ->  Index Scan using n_logins_pkey on n_logins
> (cost=0.00..3.15 rows=1 width=4) (actual time=0.011..0.011 rows=0
> loops=273486)
>               Index Cond: ("outer".login_id = n_logins.login_id)
>               Filter: (account_id = 1655)
> Total runtime: 5473.843 ms
> (8 rows)


-- 
Fduch M. Pravking

In response to

Responses

pgsql-ru-general by date

Next:From: Sergey SuleymanovDate: 2006-12-05 07:59:47
Subject: Re: SELECT ... WHERE ... IN (SELECT ...) ->
Previous:From: Alexander M. PravkingDate: 2006-12-05 07:34:07
Subject: Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... )

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group