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

> У меня с форматированием все нормально.
> Блин, тяжело по преписке пробовать...
хм... есть варианты?
> А если в этом запросе отключить 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(at)%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