увеличение записей после Materialize

Всем привет!

PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

Запрос:

select
      *
from
       (select
               *
       from
               hotcore.otahotel oh
       where
               oh.report_date >= '2015-02-01'
       ) oh
LEFT join
       public.otahotel o on o.id = oh.otahotel_id
ORDER BY oh.otahotel_id       
limit 10

план:

Limit  (cost=0.06..54.29 rows=10 width=1030) (actual time=0.305..0.687 rows=10 loops=1)
  Buffers: shared hit=36 read=11
  ->  Merge Left Join  (cost=0.06..710777541.31 rows=131056907 width=1030) (actual time=0.304..0.684 rows=10 loops=1)
        Merge Cond: (oh.otahotel_id = o.id)
        Buffers: shared hit=36 read=11
        ->  Merge Append  (cost=0.06..706448425.66 rows=131056907 width=673) (actual time=0.148..0.442 rows=10 loops=1)
              Sort Key: oh.otahotel_id
              Buffers: shared hit=21 read=9
              ->  Index Scan using inx_otahotel_otahotel_id on otahotel oh  (cost=0.00..8.27 rows=1 width=556) (actual time=0.007..0.007 rows=0 loops=1)
                    Filter: (report_date >= '2015-02-01'::date)
                    Buffers: shared hit=1
              ->  Index Scan using otahotel_2015w05_otahotel_id_idx on otahotel_2015w05 oh  (cost=0.00..225939629.96 rows=4382998 width=692) (actual time=0.038..0.154 rows=6 loops=1)
                    Filter: (report_date >= '2015-02-01'::date)
                    Buffers: shared hit=5 read=5
              ->  Index Scan using otahotel_2015w06_otahotel_id_idx on otahotel_2015w06 oh  (cost=0.00..225060498.11 rows=59753404 width=689) (actual time=0.026..0.026 rows=1 loops=1)
                    Filter: (report_date >= '2015-02-01'::date)
                    Buffers: shared hit=5
              ->  Index Scan using otahotel_2015w07_otahotel_id_idx on otahotel_2015w07 oh  (cost=0.00..210744474.09 rows=55944925 width=670) (actual time=0.026..0.026 rows=1 loops=1)
                    Filter: (report_date >= '2015-02-01'::date)
                    Buffers: shared hit=5
              ->  Index Scan using otahotel_2015w08_otahotel_id_idx on otahotel_2015w08 oh  (cost=0.00..41333125.76 rows=10975579 width=599) (actual time=0.027..0.117 rows=5 loops=1)
                    Filter: (report_date >= '2015-02-01'::date)
                    Buffers: shared hit=5 read=4
        ->  Materialize  (cost=0.00..2036902.07 rows=512915 width=356) (actual time=0.029..0.173 rows=16 loops=1)
              Buffers: shared hit=15 read=2
              ->  Index Scan using pk_otahotel on otahotel o  (cost=0.00..2035619.78 rows=512915 width=356) (actual time=0.018..0.136 rows=8 loops=1)
                    Buffers: shared hit=15 read=2
Total runtime: 1.035 ms

вопрос по части

        ->  Materialize  (cost=0.00..2036902.07 rows=512915 width=356) (actual time=0.029..0.173 rows=16 loops=1)
              Buffers: shared hit=15 read=2
              ->  Index Scan using pk_otahotel on otahotel o  (cost=0.00..2035619.78 rows=512915 width=356) (actual time=0.018..0.136 rows=8 loops=1)
                    Buffers: shared hit=15 read=2

Видим, что прочитали 8 строк, после Materialize видим 16. Как

Gold_,

Это потому, что используется Merge Join. Если у нас есть дублирующиеся значения, то это приведет к тому, что соответствующие записи будут возвращены повторно пр результатам связки. `EXPLAIN (analyze)` в каждом узле рапортует фактически возвращенное кол-во записей, соответственно если одна и та же запись возвращалась несколько раз, общее значение увеличится.

vyegorov
Gold_, Это потому, что используется Merge Join. Если у нас есть дублирующиеся значения, то это приведет к тому, что соответствующие записи будут возвращены повторно пр результатам связки. `EXPLAIN (analyze)` в каждом узле рапортует фактически возвращенное кол-во записей, соответственно если одна и та же запись возвращалась несколько раз, общее значение увеличится.

Честно говоря, не понял.

 Index Scan using pk_otahotel on otahotel o  (cost=0.00..2035619.78 rows=512915 width=356) (actual time=0.018..0.136 rows=8 loops=1)

pk_otahotel - это PRIMARY KEY
Запрос вернет 10 записей для одного o.id
Вы могли бы перефразировать свой ответ?

Gold_,

Я попытался смоделировать ситуацию. У меня 9.4.1-win64.

DROP TABLE a;
DROP TABLE b;
CREATE TABLE a AS
SELECT id,key
  FROM generate_series(1,10) id
  JOIN generate_series(1,10) key ON id>=key;
CREATE TABLE b AS SELECT id, repeat('X'::text, (random()*50)::int+100) junk FROM generate_series(1,100) id;
INSERT INTO b VALUES (10, 'xtra');
CREATE INDEX i_a ON a(id);
CREATE INDEX i_b ON b(id);
VACUUM ANALYZE a;
VACUUM ANALYZE b;

RESET ALL;
SET enable_hashjoin TO off;
SET enable_nestloop TO off;
SET enable_sort TO off;
EXPLAIN (analyze)
WITH x AS (
    SELECT id FROM a ORDER BY id
)
SELECT *
  FROM x JOIN b USING (id);

Этот `EXPLAIN` показывает, что:

  ->  Sort  (cost=10000000002.69..10000000002.83 rows=55 width=4) (actual time=0.074..0.079 rows=64 loops=1)
        ->  CTE Scan on x  (cost=0.00..1.10 rows=55 width=4) (actual time=0.022..0.055 rows=55 loops=1)

Т.е. не смотря на то, что нижний узел вернул 55 записей, сортировка отдала 64. Этот эффект возникает из-за дублирующихся значений, т.е. по “десяточкам” надо пройтись 2 раза. Merge Join Rescan называется.

Надеюсь, что пояснил. Сам лучше стал понимать :)

vyegorov,

а покажите целиком EXPLAIN Вашего теста

Gold_,

Merge Join  (cost=10000000011.80..10000000027.68 rows=56 width=127) (actual time=0.094..0.125 rows=65 loops=1)
  Merge Cond: (b.id = x.id)
  CTE x
    ->  Index Only Scan using i_a on a  (cost=0.14..8.97 rows=55 width=4) (actual time=0.008..0.020 rows=55 loops=1)
          Heap Fetches: 0
  ->  Index Scan using i_b on b  (cost=0.14..14.94 rows=101 width=127) (actual time=0.026..0.033 rows=12 loops=1)
  ->  Sort  (cost=10000000002.69..10000000002.83 rows=55 width=4) (actual time=0.061..0.065 rows=64 loops=1)
        Sort Key: x.id
        Sort Method: quicksort  Memory: 27kB
        ->  CTE Scan on x  (cost=0.00..1.10 rows=55 width=4) (actual time=0.012..0.045 rows=55 loops=1)
Planning time: 0.293 ms
Execution time: 0.195 ms

vyegorov,

Большое спасибо.