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

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
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:46:06
Message-ID: Pine.LNX.4.64.0612041945120.16338@sn.sai.msu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-ru-general

Это только у меня все строки смешались ? Абсолютно недружественный
пост, читать невозможно.

Олег
On Mon, 4 Dec 2006, 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)::bigintIndexes: "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) REFERENCESn_logins(login_id)
> ON UPDATE CASCADE "n_traffic_traftype_id_fkey" FOREIGN KEY (traftype_id)
> REFERENCESn_traftypes(traftype_id) ON UPDATE CASCADE
> engineer(at)billing=# \d n_logins Table
> "public.n_logins" Column | Type |
> Modifiers------------+------------------------+-------------------------------------------------------------
> login_id | integer | not null
> defaultnextval('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)
> REFERENCESn_accounts(account_id)Triggers: tr_after_n_logins AFTER INSERT
> OR DELETE OR UPDATE ON n_logins FOREACH ROW EXECUTE PROCEDURE
> tr_f_after_n_logins() tr_before_n_logins BEFORE UPDATE ON n_logins FOR
> EACH ROW EXECUTEPROCEDURE tr_f_before_n_logins()--engineer
> ---------------------------(end of broadcast)---------------------------TIP
> 5: don't forget to increase your free space map settings
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

In response to

Responses

Browse pgsql-ru-general by date

  From Date Subject
Next Message Teodor Sigaev 2006-12-04 16:58:11 Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT
Previous Message Anton 2006-12-04 16:42:50 SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... )