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

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

From

Teodor Sigaev

Date:

04 December 2006, 22:31:36

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 в обоих таблицах...

Anton wrote:
> Слышал, что ещё со времён 7.4 производительность запросов типа сабжа
> была значительно улучшена... Однако как раз споткнулся об него так:
> SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01
> 00:00:00'
> AND login_id IN (SELECT login_id FROM n_logins WHERE account_id = '1655')
> ORDER BY collect_time LIMIT 1
> Даёт ужас вроде 5 секунд, при этом в планах было вообще более 700 000сек..
>
> Хак типа:
> SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01
> 00:00:00'
> AND (login_id = '1240' OR login_id ='411')
> ORDER BY collect_time LIMIT 1
> даёт всего около 0.3 сек и реальность недалека от плана.
>
> Это я что-то "перепонастроил" или всё и вправду так невесело?
>
>
> Подробности:
> SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01
> 00:00:00'
> AND login_id IN (SELECT login_id FROM n_logins WHERE account_id = '1655')
> ORDER BY collect_time LIMIT 1
> --------------------------------
> Limit  (cost=0.00..2028.44 rows=1 width=8) (actual
> time=4434.532..4434.532 rows=0 loops=1)
>   ->  Nested Loop IN Join  (cost=0.00..722123.09 rows=356 width=8)
> (actual time=4434.527..4434.527 rows=0 loops=1)
>         ->  Index Scan using n_traffic_collect_time on n_traffic
> (cost=0.00..9723.29 rows=225023 width=12) (actual time=0.036..830.414
> rows=224971 loops=1)
>               Index Cond: (collect_time > '1970-01-01
> 00:00:00'::timestamp without time zone)
>         ->  Index Scan using n_logins_pkey on n_logins
> (cost=0.00..3.15 rows=1 width=4) (actual time=0.010..0.010 rows=0
> loops=224971)
>               Index Cond: ("outer".login_id = n_logins.login_id)
>               Filter: (account_id = 1655)
> Total runtime: 4434.827 ms
> (8 rows)
>
>
> SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01
> 00:00:00'
> AND (login_id = '1240' OR login_id ='411')
> ORDER BY collect_time LIMIT 1
> --------------------------------
> Limit  (cost=7.04..7.04 rows=1 width=8) (actual time=0.145..0.145
> rows=0 loops=1)
>   ->  Sort  (cost=7.04..7.04 rows=1 width=8) (actual
> time=0.139..0.139 rows=0 loops=1)
>         Sort Key: collect_time
>         ->  Bitmap Heap Scan on n_traffic  (cost=4.01..7.03 rows=1
> width=8) (actual time=0.089..0.089 rows=0 loops=1)
>               Recheck Cond: (((login_id = 1240) AND (collect_time >
> '1970-01-01 00:00:00'::timestamp without time zone)) OR ((login_id =
> 411) AND (collect_time > '1970-01-01 00:00:00'::timestamp without time
> zone)))
>               ->  BitmapOr  (cost=4.01..4.01 rows=1 width=0) (actual
> time=0.080..0.080 rows=0 loops=1)
>                     ->  Bitmap Index Scan on n_traffic_login_id_key
> (cost=0.00..2.01 rows=1 width=0) (actual time=0.043..0.043 rows=0
> loops=1)
>                           Index Cond: ((login_id = 1240) AND
> (collect_time > '1970-01-01 00:00:00'::timestamp without time zone))
>                     ->  Bitmap Index Scan on n_traffic_login_id_key
> (cost=0.00..2.01 rows=1 width=0) (actual time=0.029..0.029 rows=0
> loops=1)
>                           Index Cond: ((login_id = 411) AND
> (collect_time > '1970-01-01 00:00:00'::timestamp without time zone))
> Total runtime: 0.358 ms
> (11 rows)

--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/

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

From

Gerasimenko

Date:

04 December 2006, 22:38:40

a tak ?  (index po : n_traffic.collect_time, n_logins.account_id)

SELECT
    collect_time
FROM
    n_traffic,
    n_logins
WHERE
    collect_time is not null
    AND
    n_traffic.login_id = n_logins.login_id
    AND
    account_id = '1655'
order by collect_time limit 1;

> 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 в обоих таблицах...
>
> Anton wrote:
>> Слышал, что ещё со времён 7.4 производительность запросов типа сабжа
>> была значительно улучшена... Однако как раз споткнулся об него так:
>> SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01
>> 00:00:00'
>> AND login_id IN (SELECT login_id FROM n_logins WHERE account_id =
>> '1655')
>> ORDER BY collect_time LIMIT 1
>> Даёт ужас вроде 5 секунд, при этом в планах было вообще более 700
>> 000сек..
>>
>> Хак типа:
>> SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01
>> 00:00:00'
>> AND (login_id = '1240' OR login_id ='411')
>> ORDER BY collect_time LIMIT 1
>> даёт всего около 0.3 сек и реальность недалека от плана.
>>
>> Это я что-то "перепонастроил" или всё и вправду так невесело?
>>
>>
>> Подробности:
>> SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01
>> 00:00:00'
>> AND login_id IN (SELECT login_id FROM n_logins WHERE account_id =
>> '1655')
>> ORDER BY collect_time LIMIT 1
>> --------------------------------
>> Limit  (cost=0.00..2028.44 rows=1 width=8) (actual
>> time=4434.532..4434.532 rows=0 loops=1)
>>   ->  Nested Loop IN Join  (cost=0.00..722123.09 rows=356 width=8)
>> (actual time=4434.527..4434.527 rows=0 loops=1)
>>         ->  Index Scan using n_traffic_collect_time on n_traffic
>> (cost=0.00..9723.29 rows=225023 width=12) (actual time=0.036..830.414
>> rows=224971 loops=1)
>>               Index Cond: (collect_time > '1970-01-01
>> 00:00:00'::timestamp without time zone)
>>         ->  Index Scan using n_logins_pkey on n_logins
>> (cost=0.00..3.15 rows=1 width=4) (actual time=0.010..0.010 rows=0
>> loops=224971)
>>               Index Cond: ("outer".login_id = n_logins.login_id)
>>               Filter: (account_id = 1655)
>> Total runtime: 4434.827 ms
>> (8 rows)
>>
>>
>> SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01
>> 00:00:00'
>> AND (login_id = '1240' OR login_id ='411')
>> ORDER BY collect_time LIMIT 1
>> --------------------------------
>> Limit  (cost=7.04..7.04 rows=1 width=8) (actual time=0.145..0.145
>> rows=0 loops=1)
>>   ->  Sort  (cost=7.04..7.04 rows=1 width=8) (actual
>> time=0.139..0.139 rows=0 loops=1)
>>         Sort Key: collect_time
>>         ->  Bitmap Heap Scan on n_traffic  (cost=4.01..7.03 rows=1
>> width=8) (actual time=0.089..0.089 rows=0 loops=1)
>>               Recheck Cond: (((login_id = 1240) AND (collect_time >
>> '1970-01-01 00:00:00'::timestamp without time zone)) OR ((login_id =
>> 411) AND (collect_time > '1970-01-01 00:00:00'::timestamp without time
>> zone)))
>>               ->  BitmapOr  (cost=4.01..4.01 rows=1 width=0) (actual
>> time=0.080..0.080 rows=0 loops=1)
>>                     ->  Bitmap Index Scan on n_traffic_login_id_key
>> (cost=0.00..2.01 rows=1 width=0) (actual time=0.043..0.043 rows=0
>> loops=1)
>>                           Index Cond: ((login_id = 1240) AND
>> (collect_time > '1970-01-01 00:00:00'::timestamp without time zone))
>>                     ->  Bitmap Index Scan on n_traffic_login_id_key
>> (cost=0.00..2.01 rows=1 width=0) (actual time=0.029..0.029 rows=0
>> loops=1)
>>                           Index Cond: ((login_id = 411) AND
>> (collect_time > '1970-01-01 00:00:00'::timestamp without time zone))
>> Total runtime: 0.358 ms
>> (11 rows)
>

SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... )

From

Anton

Date:

04 December 2006, 22:42:50

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

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

From

Oleg Bartunov

Date:

04 December 2006, 22:46:06

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

Олег
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@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@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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

From

Anton

Date:

04 December 2006, 23:49:31

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

Мои извинения, возможно gmail всё слил...

> >> 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_idAND 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@billing=# \d n_traffic
> > 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@billing=# \d n_logins
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) REFERENCES
n_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

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

From

Anton

Date:

05 December 2006, 00:17:54

> У меня с форматированием все нормально.
> Блин, тяжело по преписке пробовать...
хм... есть варианты?
> А если в этом запросе отключить nested_join?
> set enable_nestloop=off;

О, да! Это в несколько раз ускорило процесс (план ниже). Однако "хак"
с OR'ами, в тех же условиях, всё же ещё гораздо быстрее...

На всякий случай, вот мои настройки (машина с 1Гб памяти, 2xPIII,
RAID5 из 6-ти SCSI дисков):
max_connections = 50
shared_buffers = 81920
temp_buffers = 57792
work_mem = 81920
maintenance_work_mem = 131072
max_fsm_pages = 262144
max_fsm_relations = 1000
wal_buffers = 64
checkpoint_segments = 4
checkpoint_timeout = 300
checkpoint_warning = 30
effective_cache_size = 6553
random_page_cost = 3
default_statistics_target = 800
log_rotation_age = 1440
log_line_prefix = '%t %u@%d '
stats_start_collector = on
stats_command_string = on
stats_block_level = on
stats_row_level = on
stats_reset_on_server_start = on


=# set enable_nestloop=off;
SET

=# explain analyze
billing-# SELECT collect_time FROM n_traffic, n_logins
billing-#  WHERE collect_time > '1970-01-01 00:00:00'
billing-#    AND n_traffic.login_id = n_logins.login_id
billing-#    AND account_id = '1655'
billing-#  ORDER BY collect_time LIMIT 1;
----------------------------------------------
 Limit  (cost=5893.09..5893.09 rows=1 width=8) (actual
time=1368.181..1368.181 rows=0 loops=1)
   ->  Sort  (cost=5893.09..5894.01 rows=366 width=8) (actual
time=1368.176..1368.176 rows=0 loops=1)
         Sort Key: n_traffic.collect_time
         ->  Hash Join  (cost=3.54..5877.51 rows=366 width=8) (actual
time=1368.138..1368.138 rows=0 loops=1)
               Hash Cond: ("outer".login_id = "inner".login_id)
               ->  Seq Scan on n_traffic  (cost=0.00..4713.70
rows=231322 width=12) (actual time=0.022..801.234 rows=231381 loops=1)
                     Filter: (collect_time > '1970-01-01
00:00:00'::timestamp without time zone)
               ->  Hash  (cost=3.53..3.53 rows=2 width=4) (actual
time=0.075..0.075 rows=2 loops=1)
                     ->  Index Scan using n_logins_account_id on
n_logins  (cost=0.00..3.53 rows=2 width=4) (actual time=0.034..0.048
rows=2 loops=1)
                           Index Cond: (account_id = 1655)
 Total runtime: 1368.493 ms
(11 rows)


=# explain analyze
billing-# SELECT collect_time FROM n_traffic
billing-#  WHERE collect_time > '1970-01-01 00:00:00'
billing-#    AND login_id = '1240' OR login_id = '411'
billing-#  ORDER BY collect_time LIMIT 1;
---------------------------------------------------------
 Limit  (cost=7.03..7.04 rows=1 width=8) (actual time=0.114..0.114
rows=0 loops=1)
   ->  Sort  (cost=7.03..7.04 rows=1 width=8) (actual
time=0.110..0.110 rows=0 loops=1)
         Sort Key: collect_time
         ->  Bitmap Heap Scan on n_traffic  (cost=4.01..7.02 rows=1
width=8) (actual time=0.080..0.080 rows=0 loops=1)
               Recheck Cond: ((login_id = 1240) OR (login_id = 411))
               Filter: (((collect_time > '1970-01-01
00:00:00'::timestamp without time zone) AND (login_id = 1240)) OR
(login_id = 411))
               ->  BitmapOr  (cost=4.01..4.01 rows=1 width=0) (actual
time=0.071..0.071 rows=0 loops=1)
                     ->  Bitmap Index Scan on n_traffic_login_id
(cost=0.00..2.00 rows=1 width=0) (actual time=0.037..0.037 rows=0
loops=1)
                           Index Cond: (login_id = 1240)
                     ->  Bitmap Index Scan on n_traffic_login_id
(cost=0.00..2.00 rows=1 width=0) (actual time=0.027..0.027 rows=0
loops=1)
                           Index Cond: (login_id = 411)
 Total runtime: 0.382 ms
(12 rows)

--
engineer

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

From

Sergey Suleymanov

Date:

05 December 2006, 12:31:13

>>>>> Anton  writes:

 Anton> explain analyze SELECT collect_time FROM n_traffic, n_logins
 Anton> WHERE collect_time > '1970-01-01 00:00:00' AND
 Anton> n_traffic.login_id = n_logins.login_id AND account_id = '1655'
 Anton> ORDER BY collect_time LIMIT 1

        А если таки явно нарисовать join ?

 SELECT n_traffic.collect_time
   FROM n_logins
        JOIN n_traffic ON (n_traffic.login_id = n_logins.login_id AND
                           n_traffic.collect_time > '1970-01-01 00:00:00')
   WHERE n_logins.account_id = '1655'
   ORDER BY n_traffic.collect_time, n_traffic.login_id LIMIT 1


--
  Sergey Suleymanov

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

From

Anton

Date:

05 December 2006, 13:01:05

>         А если таки явно нарисовать 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)
         ->  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)

--
engineer

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

From

"Alexander M. Pravking"

Date:

05 December 2006, 13:37:41

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

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

From

Sergey Suleymanov

Date:

05 December 2006, 13:59:47

>>>>> Alexander M Pravking writes:

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

            И толку? Потом ведь еще надо сортировку order by отработать.

--
  Sergey Suleymanov

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

From

Anton

Date:

05 December 2006, 14:07:29

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

Да, ничего не изменяется.
--
engineer

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

From

Sergey Suleymanov

Date:

05 December 2006, 14:10:49

>>>>> Anton  writes:

 >> А если таки явно нарисовать join ?
 Anton> то ничего не меняется, т.к. оно как раз это вроде бы само-то и
 Anton> делает.

        Тогда остается только разнести order by от join

select collect_time
  from (select collect_time
          from n_logins
               join n_traffic on (n_traffic.login_id = n_logins.login_id and
                                  n_traffic.collect_time > '1970-01-01 00:00:00')
          where n_logins.account_id = '1655' ) as q
  order by collect_time limit 1

и индекс по (login_id,collect_time)


--
  Sergey Suleymanov

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

From

Anton

Date:

05 December 2006, 15:34:00

> А если в этом запросе отключить nested_join?
> set enable_nestloop=off;

 Кажется я нашёл причину.

 Дело принимает такой долгий оборот когда в таблице n_traffic нет
записей для тех логинов по которым ищется информация.
 Может быть на основе статистики постгрес делает вывод "проверить по
полной на всякий случай" и проходит полностью по n_traffic.
А когда есть хотя бы одна запись, всё мигом делается:

=# explain analyze SELECT * FROM
billing-# (
billing(#  SELECT collect_time FROM n_traffic
billing(#  WHERE login_id IN (SELECT login_id FROM n_logins WHERE
account_id = '1655')
billing(# ) as t
billing-# WHERE collect_time > '1970-01-01' ORDER BY collect_time LIMIT 1;
---------------------------------------------------------
 Limit  (cost=0.00..2026.32 rows=1 width=8) (actual time=0.110..0.112
rows=1 loops=1)
   ->  Nested Loop IN Join  (cost=0.00..911843.38 rows=450 width=8)
(actual time=0.104..0.104 rows=1 loops=1)
         ->  Index Scan using n_traffic_collect_time_login_id on
n_traffic  (cost=0.00..11101.16 rows=284514 width=12) (actual
time=0.066..0.066 rows=1 loops=1)
               Index Cond: (collect_time > '1970-01-01
00:00:00'::timestamp without time zone)
         ->  Index Scan using n_logins_pkey on n_logins
(cost=0.00..3.15 rows=1 width=4) (actual time=0.025..0.025 rows=1
loops=1)
               Index Cond: ("outer".login_id = n_logins.login_id)
               Filter: (account_id = 1655)
 Total runtime: 0.407 ms
(8 rows)

--
engineer

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

From

"Nikolay Samokhvalov"

Date:

15 December 2006, 21:04:04

On 12/4/06, Anton <anton200@gmail.com> wrote:
> > Это только у меня все строки смешались ? Абсолютно недружественный
> > пост, читать невозможно.
>
> Мои извинения, возможно gmail всё слил...

Я замечаю ту же проблему постоянно - при общении из gmail-а с людьми
из ГАИШа :-)
Причём только с ними :-) Gmail юзаю постоянно, как основную почту.

--
Best regards,
Nikolay

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

From

"Nikolay Samokhvalov"

Date:

15 December 2006, 21:06:11

On 12/15/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
> On 12/4/06, Anton <anton200@gmail.com> wrote:
> > > Это только у меня все строки смешались ? Абсолютно недружественный
> > > пост, читать невозможно.
> >
> > Мои извинения, возможно gmail всё слил...
>

Наверное, дело в pain-е всё же. Ну то есть в "pine + gmail" :-)


--
Best regards,
Nikolay

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

From

"Nikolay Samokhvalov"

Date:

15 December 2006, 21:30:46

Весело я опечатался (pain) %)

В общем, как некоторый компромисс - предлагаю gmail-овцам писать в koi8-r и включив rich text formatting при написании. Тогда в ГАИШе читать будут нормально :-) Но появится небольшая проблемка новая - при ответе у них в pine-е будет цитирование неправильно осуществлятся, только 1 знак ">".

On 12/15/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:

On 12/15/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
> On 12/4/06, Anton <anton200@gmail.com> wrote:
> > > Это только у меня все строки смешались ? Абсолютно недружественный
> > > пост, читать невозможно.
> >
> > Мои извинения, возможно gmail всё слил...
>

Наверное, дело в pain-е всё же. Ну то есть в "pine + gmail" :-)

--
Best regards,
Nikolay

--
Best regards,
Nikolay

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

From

"Nikolay Samokhvalov"

Date:

15 December 2006, 21:41:47

Сорри за оффтопный флуд, но причина теперь совсем очевидна. Она заключается в использовании gmail-ом штуки под названием format=flowed:

Content-Type: text/plain; charset=KOI8-R; format=flowed

Про это написано тут: http://www.math.washington.edu/~chappa/pine/pine-info/misc/flowed.html
В ГАИШе pine версии 4.44. Может, обновитесь до 4.60+? GMail много кто использует...

On 12/15/06, Nikolay Samokhvalov <samokhvalov@gmail.com > wrote:

Весело я опечатался (pain) %)

В общем, как некоторый компромисс - предлагаю gmail-овцам писать в koi8-r и включив rich text formatting при написании. Тогда в ГАИШе читать будут нормально :-) Но появится небольшая проблемка новая - при ответе у них в pine-е будет цитирование неправильно осуществлятся, только 1 знак ">".

On 12/15/06, Nikolay Samokhvalov < samokhvalov@gmail.com> wrote:
On 12/15/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
> On 12/4/06, Anton < anton200@gmail.com> wrote:
> > > Это только у меня все строки смешались ? Абсолютно недружественный
> > > пост, читать невозможно.
> >
> > Мои извинения, возможно gmail всё слил...
>

Наверное, дело в pain-е всё же. Ну то есть в "pine + gmail" :-)

--
Best regards,
Nikolay

--
Best regards,
Nikolay

--
Best regards,
Nikolay