Yo!>а как тогда выбирается только кусок выборки???
http://www.sql.ru/faq/faq_topic.aspx?fid=171
покажи статистику и планы, offset из начала и конца, чем они отличаются ?
может у тебя элементарно сортировка в памяти не помещается ...
Вот тестовые запросы
explain analyze SELECT * FROM dm_s WHERE ( user_age>=18 AND user_age<=40 and sex in (2) and search_order>0 and city1=31677 and sexwant_1=TRUE ) order by search_order limit 10 offset 10;
explain analyze SELECT * FROM dm_s WHERE ( user_age>=18 AND user_age<=40 and sex in (2) and search_order>0 and city1=31677 and sexwant_1=TRUE ) order by search_order limit 10 offset 1000;
explain analyze SELECT * FROM dm_s WHERE ( user_age>=18 AND user_age<=40 and sex in (2) and search_order>0 and city1=31677 and sexwant_1=TRUE ) order by search_order limit 10 offset 10000;
explain analyze SELECT * FROM dm_s WHERE ( user_age>=18 AND user_age<=40 and sex in (2) and search_order>0 and city1=31677 and sexwant_1=TRUE ) order by search_order limit 10 offset 30000;
explain analyze SELECT count(*) FROM dm_s WHERE ( user_age>=18 AND user_age<=40 and sex in (2) and search_order>0 and city1=31677 and sexwant_1=TRUE );
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=16.84..33.69 rows=10 width=67) (actual time=0.402..0.435 rows=10 loops=1)
-> Index Scan using dm_s1_big on dm_s (cost=0.00..39657.61 rows=23543 width=67) (actual time=0.359..0.413 rows=20 loops=1)
Index Cond: ((search_order > 0) AND (user_age >= 18) AND (user_age <= 40) AND (city1 = 31677) AND (sex = 2) AND (sexwant_1 = true))
Total runtime: 0.834 ms
(записей: 4)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1684.48..1701.32 rows=10 width=67) (actual time=6.825..6.864 rows=10 loops=1)
-> Index Scan using dm_s1_big on dm_s (cost=0.00..39657.61 rows=23543 width=67) (actual time=0.342..6.287 rows=1010 loops=1)
Index Cond: ((search_order > 0) AND (user_age >= 18) AND (user_age <= 40) AND (city1 = 31677) AND (sex = 2) AND (sexwant_1 = true))
Total runtime: 7.221 ms
(записей: 4)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=16844.76..16861.60 rows=10 width=67) (actual time=80.024..80.061 rows=10 loops=1)
-> Index Scan using dm_s1_big on dm_s (cost=0.00..39657.61 rows=23543 width=67) (actual time=0.344..73.952 rows=10010 loops=1)
Index Cond: ((search_order > 0) AND (user_age >= 18) AND (user_age <= 40) AND (city1 = 31677) AND (sex = 2) AND (sexwant_1 = true))
Total runtime: 80.364 ms
(записей: 4)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=39657.61..39657.61 rows=1 width=67) (actual time=348.707..348.742 rows=10 loops=1)
-> Index Scan using dm_s1_big on dm_s (cost=0.00..39657.61 rows=23543 width=67) (actual time=0.345..329.126 rows=30010 loops=1)
Index Cond: ((search_order > 0) AND (user_age >= 18) AND (user_age <= 40) AND (city1 = 31677) AND (sex = 2) AND (sexwant_1 = true))
Total runtime: 349.061 ms
(записей: 4)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=39716.47..39716.47 rows=1 width=0) (actual time=761.276..761.278 rows=1 loops=1)
-> Index Scan using dm_s1_big on dm_s (cost=0.00..39657.61 rows=23543 width=0) (actual time=0.325..705.573 rows=39074 loops=1)
Index Cond: ((search_order > 0) AND (user_age >= 18) AND (user_age <= 40) AND (city1 = 31677) AND (sex = 2) AND (sexwant_1 = true))
Total runtime: 762.558 ms
(записей: 4)
как видно, чем больше сдвиг по выборке - тем дольше работаем...
и count отвратительно долго считается.
Я прочитал ссылку - не понятно, почему такая конструкция работает быстрее, чем limit-offset
