Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... ) - Mailing list pgsql-ru-general

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