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

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

From: Teodor Sigaev <teodor(at)sigaev(dot)ru>
To: Anton <anton200(at)gmail(dot)com>
Cc: pgsql-ru-general(at)postgresql(dot)org
Subject: Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT
Date: 2006-12-04 16:58:11
Message-ID: 457453A3.1060207@sigaev.ru (view raw or flat)
Thread:
Lists: pgsql-ru-general
Индекс по ( collect_time, login_id ) не поможет?

Судя по планам, постгрес мучается с выводом join'a - он не сортирован
по collect_time.

Anton wrote:
>> SELECT
>>         collect_time
>> FROM
>>         n_traffic,
>>         n_logins
>> WHERE
>>         collect_time > '1970-01-01 00:00:00'
>>         AND
>>         n_traffic.login_id = n_logins.login_id
>>         AND
>>         account_id = '1655';
>>
>> А так не лучше? C индексами по login_id в обоих таблицах...
> 
> Всё замечательно до тех пор, пока не добавляется ORDER BY collect_time 
> LIMIT 1;
> А без (ORDER BY collect_time LIMIT 1) замечательно отрабатывает и Ваш
> вариант, и IN.
> 
> Вся штука, что сортировка и лимит почему-то плохо работают с
> указанными вариациями (по сути ведь это JOIN, так ведь...), когда для
> account_id =  '...' есть БОЛЬШЕ ЧЕМ ОДИН login_id.
> Когда login_id всего один, все варианты довольно быстры.
> 
> На всякий случай:
> engineer(at)billing=# \d n_traffic
>                         Table "public.n_traffic"
>    Column    |            Type             |          Modifiers
> --------------+-----------------------------+------------------------------
> login_id     | integer                     | not null
> traftype_id  | integer                     | not null
> collect_time | timestamp without time zone | not null default now()
> bytes_in     | bigint                      | not null default (0)::bigint
> bytes_out    | bigint                      | not null default (0)::bigint
> Indexes:
>    "n_traffic_login_id_key" UNIQUE, btree (login_id, traftype_id, 
> collect_time)
>    "n_traffic_collect_time" btree (collect_time)
>    "n_traffic_collect_time_month" btree (date_trunc('month'::text,
> collect_time))
>    "n_traffic_login_id" btree (login_id)
> Foreign-key constraints:
>    "n_traffic_login_id_fkey" FOREIGN KEY (login_id) REFERENCES
> n_logins(login_id) ON UPDATE CASCADE
>    "n_traffic_traftype_id_fkey" FOREIGN KEY (traftype_id) REFERENCES
> n_traftypes(traftype_id) ON UPDATE CASCADE
> 
> engineer(at)billing=# \d n_logins
>                                      Table "public.n_logins"
>   Column   |          Type          |                          Modifiers
> ------------+------------------------+------------------------------------------------------------- 
> 
> login_id   | integer                | not null default
> nextval('n_logins_login_id_seq'::regclass)
> account_id | integer                | not null
> login      | character varying(255) | not null
> pwd        | character varying(128) |
> Indexes:
>    "n_logins_pkey" PRIMARY KEY, btree (login_id)
>    "n_logins_login_key" UNIQUE, btree ("login")
>    "n_logins_account_id" btree (account_id)
> Foreign-key constraints:
>    "n_logins_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> n_accounts(account_id)
> Triggers:
>    tr_after_n_logins AFTER INSERT OR DELETE OR UPDATE ON n_logins FOR
> EACH ROW EXECUTE PROCEDURE tr_f_after_n_logins()
>    tr_before_n_logins BEFORE UPDATE ON n_logins FOR EACH ROW EXECUTE
> PROCEDURE tr_f_before_n_logins()
> -- 
> engineer
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

-- 
Teodor Sigaev                                   E-mail: teodor(at)sigaev(dot)ru
                                                    WWW: http://www.sigaev.ru/

In response to

Responses

pgsql-ru-general by date

Next:From: AntonDate: 2006-12-04 17:49:31
Subject: Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... )
Previous:From: Oleg BartunovDate: 2006-12-04 16:46:06
Subject: Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT

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