rows estimate in explain analyze for the BRIN index

Поиск
Список
Период
Сортировка
От Oleksii Kliukin
Тема rows estimate in explain analyze for the BRIN index
Дата
Msg-id 036AD150-4377-49B6-B93D-A8DAF5FC0BEE@hintbits.com
обсуждение исходный текст
Ответы Re: rows estimate in explain analyze for the BRIN index  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
<div class="">Hi,</div><div class=""><br class="" /></div><div class="">While experimenting with BRIN on PostgreSQL
9.5RC1I came across the following plan (which is, btw a very good example of how BRIN rocks for the clustered data, the
sizeof this table is around 90GB, the size of the index is around 3MB):</div><div class=""><br class="" /></div><div
class="">explain(analyze, buffers, verbose) select 1 from example where event_time = now() - interval '5
months';</div><divclass="">                                                                      QUERY PLAN</div><div
class="">-------------------------------------------------------------------------------------------------------------------------------------------------------</div><div
class=""> BitmapHeap Scan on example  (cost=744.44..757.64 rows=6 width=0) (actual time=73.895..73.895 rows=0
loops=1)</div><divclass="">   Output: 1</div><div class="">   Recheck Cond: (example.event_time = (now() - '5
mons'::interval))</div><divclass="">   Rows Removed by Index Recheck: 4030</div><div class="">   Heap Blocks:
lossy=128</div><divclass="">   Buffers: shared hit=629</div><div class="">   ->  Bitmap Index Scan on
example_event_time_idx1 (cost=0.00..744.41 rows=6 width=0) (actual time=70.335..70.335 rows=1280 loops=1)</div><div
class="">        Index Cond: (example.event_time = (now() - '5 mons'::interval))</div><div class="">         Buffers:
sharedhit=501</div><div class=""> Planning time: 0.125 ms</div><div class=""> Execution time: 73.943 ms</div><div
class="">(11rows)</div><div class=""><br class="" /></div><div class="">Time: 74.642 ms</div><div class=""><br class=""
/></div><divclass=""><br class="" /></div><div class="">Here EXPLAIN ANALYZE reports 1280 rows from the Bitmap Index
Scan,but on the higher level, 4030 rows were removed by Index Recheck. </div><div class=""><br class="" /></div><div
class="">Thequestions are:</div><div class=""><br class="" /></div><div class="">- how does it get 1280 rows from the
BRINindex scan, given that BRIN only stores pointers to the heap blocks, not individual rows. Does it calculate the
numberof rows in the blocks returned?</div><div class=""><br class="" /></div><div class="">- how comes that the
subsequentrecheck filters out 4030 rows, out of supposedly 1280 returned?</div><div class=""><br class="" /></div><div
class="">Kindregards,</div><div class=""><div class="" style="color: rgb(0, 0, 0); letter-spacing: normal; orphans:
auto;text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px;
-webkit-text-stroke-width:0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break:
after-white-space;">--</div><divclass="" style="color: rgb(0, 0, 0); letter-spacing: normal; orphans: auto; text-align:
start;text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px;
-webkit-text-stroke-width:0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break:
after-white-space;">Oleksii</div></div><brclass="" /> 

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: Patch: fix lock contention for HASHHDR.mutex
Следующее
От: Aleksander Alekseev
Дата:
Сообщение: Re: Patch: fix lock contention for HASHHDR.mutex