Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

Недокументированная фича

Posted Июль 16th, 2015 by bRUtality
in

Всем доброго времени!

Надеюсь, название темы привлекло внимание экспертов :)

С Постгресом знаком давно, но только сейчас наткнулся на "интересное" поведение.

Есть большая таблица, точнее, таблица-мастер и таблицы-потомки. Партицирование идет по полю, хранящему дату, на это же поле навешан индекс. Хотя, может партицирование здесь и не при чем.

Этот запрос отрабатывает влет:

SELECT * FROM master_table WHERE mydate >= 'вчера'

а этот запрос около 10 минут:
SELECT * FROM master_table WHERE mydate >= 'вчера' AND mydate <= 'сегодня'

хотя, как вы понимаете, оба запроса работают на одном и том же подмножестве.

Для чистоты эксперимента кэш сбрасывал.

Есть какие мысли?

‹ Неожиданная сортировка по timestamp update jsonb ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Размести сюда EXPLAIN ANALYSE

On Июль 16th, 2015 altair says:

Размести сюда
EXPLAIN ANALYSE

explain analyze

On Июль 16th, 2015 bRUtality says:

Пришлось добавить еще одно поле - msisdn (тоже индексированное) в условие выборки.
Для первого (быстрого) запроса планировщик дает такую картину:

Result  (cost=0.00..668238.78 rows=135624 width=365) (actual time=218.611..2442.617 rows=6147 loops=1)
  ->  Append  (cost=0.00..668238.78 rows=135624 width=365) (actual time=218.606..2409.278 rows=6147 loops=1)
        ->  Seq Scan ON master_table  (cost=0.00..0.00 rows=1 width=365) (actual time=0.004..0.004 rows=0 loops=1)
              Filter: ((mydate >= '2015-07-14'::date) AND (msisdn = 79200392997::bigint))
        ->  INDEX Scan USING child_table_y2015m07d14reg0_msisdn ON child_table_y2015m07d14reg0 master_table  (cost=0.00..4.26 rows=1 width=365) (actual time=0.056..0.056 rows=0 loops=1)
              INDEX Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg32 master_table  (cost=306.20..19872.12 rows=4034 width=365) (actual time=5.281..5.281 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg32_msisdn  (cost=0.00..305.19 rows=12101 width=0) (actual time=5.275..5.275 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg33 master_table  (cost=752.44..48048.33 rows=9750 width=365) (actual time=12.802..12.802 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg33_msisdn  (cost=0.00..750.00 rows=29251 width=0) (actual time=12.797..12.797 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg40 master_table  (cost=255.67..16881.11 rows=3427 width=365) (actual time=0.106..0.106 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg40_msisdn  (cost=0.00..254.81 rows=10282 width=0) (actual time=0.100..0.100 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg46 master_table  (cost=259.50..16830.63 rows=3416 width=365) (actual time=16.537..16.537 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg46_msisdn  (cost=0.00..258.64 rows=10249 width=0) (actual time=16.532..16.532 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg52 master_table  (cost=1460.04..93053.83 rows=18883 width=365) (actual time=183.799..1966.137 rows=3858 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg52_msisdn  (cost=0.00..1455.32 rows=56649 width=0) (actual time=181.926..181.926 rows=3858 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg57 master_table  (cost=169.79..11099.45 rows=2253 width=365) (actual time=0.059..0.059 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg57_msisdn  (cost=0.00..169.23 rows=6759 width=0) (actual time=0.052..0.052 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg62 master_table  (cost=663.10..42395.74 rows=8604 width=365) (actual time=28.242..28.242 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg62_msisdn  (cost=0.00..660.95 rows=25811 width=0) (actual time=28.236..28.236 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg71 master_table  (cost=294.80..19171.25 rows=3891 width=365) (actual time=0.050..0.050 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg71_msisdn  (cost=0.00..293.83 rows=11674 width=0) (actual time=0.044..0.044 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  INDEX Scan USING child_table_y2015m07d15reg0_msisdn ON child_table_y2015m07d15reg0 master_table  (cost=0.00..4.26 rows=1 width=365) (actual time=0.018..0.018 rows=0 loops=1)
              INDEX Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg32 master_table  (cost=305.95..19820.81 rows=4023 width=365) (actual time=0.088..0.088 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg32_msisdn  (cost=0.00..304.94 rows=12069 width=0) (actual time=0.083..0.083 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg33 master_table  (cost=688.48..44349.65 rows=9001 width=365) (actual time=0.493..0.493 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg33_msisdn  (cost=0.00..686.23 rows=27003 width=0) (actual time=0.487..0.487 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg40 master_table  (cost=253.52..16850.03 rows=3421 width=365) (actual time=0.417..0.417 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg40_msisdn  (cost=0.00..252.66 rows=10264 width=0) (actual time=0.411..0.411 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg46 master_table  (cost=239.86..15665.12 rows=3180 width=365) (actual time=10.952..10.952 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg46_msisdn  (cost=0.00..239.06 rows=9540 width=0) (actual time=10.946..10.946 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg52 master_table  (cost=1340.75..85340.76 rows=17317 width=365) (actual time=86.096..144.203 rows=1459 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg52_msisdn  (cost=0.00..1336.42 rows=51952 width=0) (actual time=85.751..85.751 rows=1459 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg57 master_table  (cost=170.13..11169.30 rows=2267 width=365) (actual time=7.042..7.042 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg57_msisdn  (cost=0.00..169.56 rows=6802 width=0) (actual time=7.036..7.036 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg62 master_table  (cost=595.62..38379.33 rows=7789 width=365) (actual time=13.648..13.648 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg62_msisdn  (cost=0.00..593.67 rows=23368 width=0) (actual time=13.642..13.642 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg71 master_table  (cost=283.25..18433.18 rows=3742 width=365) (actual time=0.066..0.066 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg71_msisdn  (cost=0.00..282.32 rows=11225 width=0) (actual time=0.059..0.059 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  INDEX Scan USING child_table_y2015m07d16reg0_msisdn ON child_table_y2015m07d16reg0 master_table  (cost=0.00..4.26 rows=1 width=365) (actual time=0.479..0.479 rows=0 loops=1)
              INDEX Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg32 master_table  (cost=213.09..13843.77 rows=2810 width=365) (actual time=0.192..0.192 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg32_msisdn  (cost=0.00..212.39 rows=8430 width=0) (actual time=0.186..0.186 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg33 master_table  (cost=378.94..24581.19 rows=4989 width=365) (actual time=0.244..0.244 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg33_msisdn  (cost=0.00..377.69 rows=14968 width=0) (actual time=0.239..0.239 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg40 master_table  (cost=174.09..11579.02 rows=2351 width=365) (actual time=0.173..0.173 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg40_msisdn  (cost=0.00..173.50 rows=7053 width=0) (actual time=0.168..0.168 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg46 master_table  (cost=169.45..11022.70 rows=2237 width=365) (actual time=0.211..0.211 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg46_msisdn  (cost=0.00..168.89 rows=6712 width=0) (actual time=0.189..0.189 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg52 master_table  (cost=731.27..46984.93 rows=9536 width=365) (actual time=6.473..163.088 rows=830 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg52_msisdn  (cost=0.00..728.88 rows=28607 width=0) (actual time=6.301..6.301 rows=830 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg57 master_table  (cost=117.53..7724.88 rows=1568 width=365) (actual time=0.185..0.185 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg57_msisdn  (cost=0.00..117.13 rows=4705 width=0) (actual time=0.179..0.179 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg62 master_table  (cost=338.65..22040.27 rows=4474 width=365) (actual time=6.361..6.361 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg62_msisdn  (cost=0.00..337.53 rows=13422 width=0) (actual time=6.355..6.355 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg71 master_table  (cost=201.43..13088.60 rows=2657 width=365) (actual time=1.714..1.714 rows=0 loops=1)
              Recheck Cond: (msisdn = 79200392997::bigint)
              Filter: (mydate >= '2015-07-14'::date)
              ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg71_msisdn  (cost=0.00..200.77 rows=7970 width=0) (actual time=1.708..1.708 rows=0 loops=1)
                    INDEX Cond: (msisdn = 79200392997::bigint)
Total runtime: 2465.377 ms

Для второго (медленного):
Result  (cost=0.00..25270.88 rows=2038 width=365) (actual time=7925.289..19235.444 rows=857 loops=1)
  ->  Append  (cost=0.00..25270.88 rows=2038 width=365) (actual time=7925.283..19231.854 rows=857 loops=1)
        ->  Seq Scan ON master_table  (cost=0.00..0.00 rows=1 width=365) (actual time=0.003..0.003 rows=0 loops=1)
              Filter: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date) AND (msisdn = 79990717388::bigint))
        ->  INDEX Scan USING child_table_y2015m07d14reg0_msisdn ON child_table_y2015m07d14reg0 master_table  (cost=0.00..4.26 rows=1 width=365) (actual time=0.151..0.151 rows=0 loops=1)
              INDEX Cond: (msisdn = 79990717388::bigint)
              Filter: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg32 master_table  (cost=628.66..749.81 rows=61 width=365) (actual time=0.216..0.216 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=628.66..628.66 rows=61 width=0) (actual time=0.211..0.211 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg32_msisdn  (cost=0.00..305.19 rows=12101 width=0) (actual time=0.209..0.209 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg32_date  (cost=0.00..323.18 rows=12101 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg33 master_table  (cost=1536.75..1826.73 rows=146 width=365) (actual time=11.043..11.043 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=1536.75..1536.75 rows=146 width=0) (actual time=11.038..11.038 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg33_msisdn  (cost=0.00..750.00 rows=29251 width=0) (actual time=11.035..11.035 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg33_date  (cost=0.00..786.43 rows=29251 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg40 master_table  (cost=523.39..624.69 rows=51 width=365) (actual time=0.090..0.090 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=523.39..523.39 rows=51 width=0) (actual time=0.086..0.086 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg40_msisdn  (cost=0.00..254.81 rows=10282 width=0) (actual time=0.084..0.084 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg40_date  (cost=0.00..268.30 rows=10282 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg46 master_table  (cost=530.95..632.24 rows=51 width=365) (actual time=0.146..0.146 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=530.95..530.95 rows=51 width=0) (actual time=0.142..0.142 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg46_msisdn  (cost=0.00..258.64 rows=10249 width=0) (actual time=0.138..0.138 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg46_date  (cost=0.00..272.03 rows=10249 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg52 master_table  (cost=2987.35..3549.43 rows=283 width=365) (actual time=7913.616..8294.157 rows=385 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=2987.35..2987.35 rows=283 width=0) (actual time=7913.398..7913.398 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg52_msisdn  (cost=0.00..1455.32 rows=56649 width=0) (actual time=34.892..34.892 rows=385 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg52_date  (cost=0.00..1531.64 rows=56649 width=0) (actual time=7848.847..7848.847 rows=27170597 loops=1)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg57 master_table  (cost=347.49..415.01 rows=34 width=365) (actual time=0.217..0.217 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=347.49..347.49 rows=34 width=0) (actual time=0.196..0.196 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg57_msisdn  (cost=0.00..169.23 rows=6759 width=0) (actual time=0.182..0.182 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg57_date  (cost=0.00..177.99 rows=6759 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg62 master_table  (cost=1356.15..1612.36 rows=129 width=365) (actual time=0.173..0.173 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=1356.15..1356.15 rows=129 width=0) (actual time=0.166..0.166 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg62_msisdn  (cost=0.00..660.95 rows=25811 width=0) (actual time=0.162..0.162 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg62_date  (cost=0.00..694.88 rows=25811 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d14reg71 master_table  (cost=604.87..720.07 rows=58 width=365) (actual time=0.341..0.341 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=604.87..604.87 rows=58 width=0) (actual time=0.313..0.313 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg71_msisdn  (cost=0.00..293.83 rows=11674 width=0) (actual time=0.306..0.306 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d14reg71_date  (cost=0.00..310.76 rows=11674 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  INDEX Scan USING child_table_y2015m07d15reg0_msisdn ON child_table_y2015m07d15reg0 master_table  (cost=0.00..4.26 rows=1 width=365) (actual time=0.056..0.056 rows=0 loops=1)
              INDEX Cond: (msisdn = 79990717388::bigint)
              Filter: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg32 master_table  (cost=626.08..745.25 rows=60 width=365) (actual time=0.474..0.474 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=626.08..626.08 rows=60 width=0) (actual time=0.454..0.454 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg32_msisdn  (cost=0.00..304.94 rows=12069 width=0) (actual time=0.434..0.434 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg32_date  (cost=0.00..320.85 rows=12069 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg33 master_table  (cost=1409.70..1677.83 rows=135 width=365) (actual time=11.713..11.713 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=1409.70..1409.70 rows=135 width=0) (actual time=11.706..11.706 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg33_msisdn  (cost=0.00..686.23 rows=27003 width=0) (actual time=11.700..11.700 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg33_date  (cost=0.00..723.14 rows=27003 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg40 master_table  (cost=521.05..622.34 rows=51 width=365) (actual time=0.361..0.361 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=521.05..521.05 rows=51 width=0) (actual time=0.355..0.355 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg40_msisdn  (cost=0.00..252.66 rows=10264 width=0) (actual time=0.350..0.350 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg40_date  (cost=0.00..268.11 rows=10264 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg46 master_table  (cost=492.03..587.37 rows=48 width=365) (actual time=0.294..0.294 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=492.03..492.03 rows=48 width=0) (actual time=0.288..0.288 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg46_msisdn  (cost=0.00..239.06 rows=9540 width=0) (actual time=0.284..0.284 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg46_date  (cost=0.00..252.70 rows=9540 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg52 master_table  (cost=2739.86..3256.26 rows=260 width=365) (actual time=6384.817..6388.614 rows=251 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=2739.86..2739.86 rows=260 width=0) (actual time=6384.711..6384.711 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg52_msisdn  (cost=0.00..1336.42 rows=51952 width=0) (actual time=9.301..9.301 rows=251 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg52_date  (cost=0.00..1403.06 rows=51952 width=0) (actual time=6366.265..6366.265 rows=24879500 loops=1)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg57 master_table  (cost=350.26..417.79 rows=34 width=365) (actual time=0.098..0.098 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=350.26..350.26 rows=34 width=0) (actual time=0.094..0.094 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg57_msisdn  (cost=0.00..169.56 rows=6802 width=0) (actual time=0.090..0.090 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg57_date  (cost=0.00..180.43 rows=6802 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg62 master_table  (cost=1217.54..1449.91 rows=117 width=365) (actual time=0.083..0.083 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=1217.54..1217.54 rows=117 width=0) (actual time=0.080..0.080 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg62_msisdn  (cost=0.00..593.67 rows=23368 width=0) (actual time=0.077..0.077 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg62_date  (cost=0.00..623.55 rows=23368 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d15reg71 master_table  (cost=580.71..691.94 rows=56 width=365) (actual time=0.104..0.104 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=580.71..580.71 rows=56 width=0) (actual time=0.100..0.100 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg71_msisdn  (cost=0.00..282.32 rows=11225 width=0) (actual time=0.096..0.096 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d15reg71_date  (cost=0.00..298.12 rows=11225 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  INDEX Scan USING child_table_y2015m07d16reg0_msisdn ON child_table_y2015m07d16reg0 master_table  (cost=0.00..4.26 rows=1 width=365) (actual time=0.021..0.021 rows=0 loops=1)
              INDEX Cond: (msisdn = 79990717388::bigint)
              Filter: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg32 master_table  (cost=435.92..519.34 rows=42 width=365) (actual time=0.197..0.197 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=435.92..435.92 rows=42 width=0) (actual time=0.193..0.193 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg32_msisdn  (cost=0.00..212.39 rows=8430 width=0) (actual time=0.189..0.189 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg32_date  (cost=0.00..223.26 rows=8430 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg33 master_table  (cost=776.77..925.73 rows=75 width=365) (actual time=0.096..0.096 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=776.77..776.77 rows=75 width=0) (actual time=0.092..0.092 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg33_msisdn  (cost=0.00..377.69 rows=14968 width=0) (actual time=0.089..0.089 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg33_date  (cost=0.00..398.79 rows=14968 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg40 master_table  (cost=356.74..426.26 rows=35 width=365) (actual time=0.091..0.091 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=356.74..356.74 rows=35 width=0) (actual time=0.085..0.085 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg40_msisdn  (cost=0.00..173.50 rows=7053 width=0) (actual time=0.082..0.082 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg40_date  (cost=0.00..182.97 rows=7053 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg46 master_table  (cost=346.67..414.19 rows=34 width=365) (actual time=0.156..0.156 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=346.67..346.67 rows=34 width=0) (actual time=0.153..0.153 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg46_msisdn  (cost=0.00..168.89 rows=6712 width=0) (actual time=0.149..0.149 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg46_date  (cost=0.00..177.52 rows=6712 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg52 master_table  (cost=1498.99..1783.01 rows=143 width=365) (actual time=4516.779..4519.595 rows=221 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=1498.99..1498.99 rows=143 width=0) (actual time=4516.711..4516.711 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg52_msisdn  (cost=0.00..728.88 rows=28607 width=0) (actual time=0.235..0.235 rows=221 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg52_date  (cost=0.00..769.79 rows=28607 width=0) (actual time=4511.885..4511.885 rows=13617137 loops=1)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg57 master_table  (cost=242.19..289.85 rows=24 width=365) (actual time=0.093..0.093 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=242.19..242.19 rows=24 width=0) (actual time=0.089..0.089 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg57_msisdn  (cost=0.00..117.13 rows=4705 width=0) (actual time=0.085..0.085 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg57_date  (cost=0.00..124.79 rows=4705 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg62 master_table  (cost=694.61..827.69 rows=67 width=365) (actual time=0.053..0.053 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=694.61..694.61 rows=67 width=0) (actual time=0.049..0.049 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg62_msisdn  (cost=0.00..337.53 rows=13422 width=0) (actual time=0.046..0.046 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg62_date  (cost=0.00..356.80 rows=13422 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  Bitmap Heap Scan ON child_table_y2015m07d16reg71 master_table  (cost=413.56..493.01 rows=40 width=365) (actual time=0.064..0.064 rows=0 loops=1)
              Recheck Cond: ((msisdn = 79990717388::bigint) AND (mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              ->  BitmapAnd  (cost=413.56..413.56 rows=40 width=0) (actual time=0.060..0.060 rows=0 loops=1)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg71_msisdn  (cost=0.00..200.77 rows=7970 width=0) (actual time=0.057..0.057 rows=0 loops=1)
                          INDEX Cond: (msisdn = 79990717388::bigint)
                    ->  Bitmap INDEX Scan ON child_table_y2015m07d16reg71_date  (cost=0.00..212.52 rows=7970 width=0) (never executed)
                          INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
Total runtime: 19238.858 ms

Никакого криминала не вижу...

Сущий ад. Это даже невозможно

On Июль 17th, 2015 altair says:

Сущий ад.

Это даже невозможно прочесть. Просто сравни. Первый случай как у тебя:

"Index Scan using order_createdon on blaorder  (cost=0.43..11423.61 rows=12319 width=274) (actual time=0.011..0.111 rows=480 loops=1)"
"  Index Cond: (createdon > '2015-01-01 00:00:00'::timestamp without time zone)"
"Total runtime: 0.142 ms"

Второй случай как у тебя:

"Index Scan using order_createdon on blaorder  (cost=0.43..1997.52 rows=1943 width=274) (actual time=0.015..0.028 rows=29 loops=1)"
"  Index Cond: ((createdon > '2015-01-01 00:00:00'::timestamp without time zone) AND (createdon < '2015-02-01 00:00:00'::timestamp without time zone))"
"Total runtime: 0.069 ms"

Что за child_table, если поиск идёт только по parent. Что за дикое количество индексов? Они ещё и "горизонтальные"...
Тебе надо вообще индексы отключить, иначе тут просто даже не разобраться.
Или это всё представление, в котором много join'ов...

это партицирование

On Июль 17th, 2015 bRUtality says:

Обычное партицирование по дате + региональному признаку.
master_table - родительская таблица
child_table_yyyyyMMdd_regNN - партиция, разделение идет по индексированным полям.
Если смотреть explain, то видно, что планировщик направляет поиск в нужные партиции.
Иначе как ворочать базой на пару терабайт? :)

В первом случае ходит по

On Июль 17th, 2015 Alarm says:

В первом случае ходит по индексу
Filter: (mydate >= '2015-07-14'::date)
-> Bitmap INDEX Scan ON child_table_y2015m07d14reg32_msisdn (cost=0.00..305.19 rows=12101 width=0) (actual time=5.275..5.275 rows=0 loops=1)
INDEX Cond: (msisdn = 79200392997::bigint), и фильтр по дате

во втором
используются два индекса
-> Bitmap INDEX Scan ON child_table_y2015m07d14reg32_msisdn (cost=0.00..305.19 rows=12101 width=0) (actual time=0.209..0.209 rows=0 loops=1)
INDEX Cond: (msisdn = 79990717388::bigint)
-> Bitmap INDEX Scan ON child_table_y2015m07d14reg32_date (cost=0.00..323.18 rows=12101 width=0) (never executed)
INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))

и merge по BitmapAnd, есть некоторые пояснения тут http://www.postgresql.org/message-id/12553.1135634231@sss.pgh.pa.us

и тут решение http://habrahabr.ru/post/169751/

спасибо

On Июль 17th, 2015 bRUtality says:

Большое вам спасибо за ответ и материал - начинаю изучать.

pg_hint_plan

On Июль 23rd, 2015 bRUtality says:

Когда-то я уже пробовал использовать этот модуль, но ничего хорошего не получилось - планировщик у Постгреса все ж не глупый.
Если я правильно понял ваш совет, вы предложили с помощью pg_hint_plan заменить Bitmap INDEX Scan на Index Scan. Я это сделал и сначала посмотрел explain - планировщик отобразил очень высокий cost (10 нулей). Сам запрос считается полчаса и, наверное, я его закенселю - планировщик со стоимостью, похоже, не обманул.

Result  (cost=10000000000.00..10000923143.08 rows=2345 width=365)
  ->  Append  (cost=10000000000.00..10000923143.08 rows=2345 width=365)
        ->  Seq Scan ON master_table  (cost=10000000000.00..10000000000.00 rows=1 width=365)
              Filter: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date) AND (msisdn = 79990717388::bigint))
        ->  INDEX Scan USING child_table_y2015m07d14reg0_msisdn ON child_table_y2015m07d14reg0 master_table  (cost=0.00..4.26 rows=1 width=365)
              INDEX Cond: (msisdn = 79990717388::bigint)
              Filter: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
        ->  INDEX Scan USING child_table_y2015m07d14reg32_date ON child_table_y2015m07d14reg32 master_table  (cost=0.00..23849.54 rows=61 width=365)
              INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              Filter: (msisdn = 79990717388::bigint)
        ->  INDEX Scan USING child_table_y2015m07d14reg33_date ON child_table_y2015m07d14reg33 master_table  (cost=0.00..57654.81 rows=146 width=365)
              INDEX Cond: ((mydate >= '2015-07-14'::date) AND (mydate <= '2015-07-16'::date))
              Filter: (msisdn = 79990717388::bigint)

Разница между доступами к

On Июль 24th, 2015 Alarm says:

Разница между доступами к данным по индексам http://use-the-index-luke.com/sql/explain-plan/postgresql/operations,
По идее нужно заставить ипользовать Bitmap Index Scan / Bitmap Heap Scan

Bitmap Heap Scan

On Июль 27th, 2015 bRUtality says:

Увы, не прокатило с BitmapHeapScan. Считалось даже немного дольше.

интересно а как такой

On Июль 28th, 2015 Alarm says:

интересно а как такой вариант
select * from (
select * from table where date >= mindate
) where date <= maxdate

Хорошая идея  Но планер

On Июль 28th, 2015 bRUtality says:

Хорошая идея :) Но планер обмануть не удалось - expalin у этих запросов один-в-один и считалось, соответственно, столько же долго.

по идее с with прокатит with

On Июль 28th, 2015 Alarm says:

по идее с with прокатит
with q as (select * from table where date >= mindate)
select * from q where date <= maxdate

Бинго! Пока не знаю почему,

On Июль 28th, 2015 bRUtality says:

Бинго!
Пока не знаю почему, но Постгрес явно не любит условие на дату в обе стороны. Вот если только "меньше" либо только "больше", то все работает быстро.
Вы конструкцией with как раз сымитировали то, что он обрабатывает, и получился хороший результат.

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023