Re: Bitmap Index Scan when btree index created

Share with you.

Access methods (sequential scan, index scan, bitmap scan, index-only scan).

1.Sequential scans
The sequential scan scans the whole table sequentially to retrieve the
required rows
from the table. The planner selects the sequential scan when a query is
retrieving
large number of rows from the table and the number of appropriate indexes
found.
The following is an example of a sequential scan where a query has to
select all
the history table's records where hist_id is greater than 1000. There is an
index
defined for the hist_id column but it won't help in this case:
warehouse_db=# EXPLAIN SELECT * FROM record.history WHERE
history_id > 1000;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on history (cost=0.00..2184580.00 rows=99998968
width=46)
Filter: (history_id > 1000)
Planning time: 57.910 ms
(3 rows)

2.Index scans
An index is a way to efficiently retrieve specific rows from database. The
planner
chooses an index scan if any index satisfies the WHERE condition. It is
faster than
the normal table scan because it does not traverse the whole set of column
of rows.
Normally, an index is created on tables with lesser number of columns. In
index
scans, PostgreSQL picks only one tuple's pointer and accesses the tuple/row
from
the table.
An index based on all columns of table has no performance benefit.
Here is an example of an index scan:
warehouse_db=# EXPLAIN SELECT * FROM record.history WHERE
history_id=1000;
QUERY PLAN
------------------------------------------------------------------
Index Scan using idx on history (cost=0.57..8.59 rows=1
width=46)
Index Cond: (history_id = 1000)
Planning time: 0.142 ms
(3 rows)

3.Index-only scans
If all the columns of a query are part of the index, then the planner
selects index-only
scans. In this case, the tuple on the page is visible, so tuples are picked
from an index
instead of a heap, which is a really big performance boost. The following
example
shows an index-only scan:
warehouse_db=# EXPLAIN SELECT history_id FROM record.history WHERE
history_id = 1000;
QUERY PLAN
------------------------------------------------------------------
Index Only Scan using idx on history (cost=0.57..8.59 rows=1
width=4)
Index Cond: (history_id = 1000)
Planning time: 0.121 ms
(3 rows)
In the preceding example, all the columns in the target and qual lists are
part of the
index; therefore, the planner selects index-only scans.

4.Bitmap scans
Unlike the index scan, the bitmap scan fetches all the tuple-pointers from
the disks
and fetches the tuple using the bitmap data structure. A bitmap scan is
useful only
when small numbers of rows are needed. Here is an example of a bitmap scan
in
which we get rows that have hist_id as 1000 or 20000:
warehouse_db=# EXPLAIN SELECT * FROM record.history WHERE
history_id = 1000 AND history_id = 20000;
QUERY PLAN
------------------------------------------------------------------
Result (cost=4.58..8.59 rows=1 width=46)
One-Time Filter: false
-> Bitmap Heap Scan on history (cost=4.58..8.59 rows=1
width=46)
Recheck Cond: (history_id = 1000)
-> Bitmap Index Scan on idx (cost=0.00..4.58 rows=1
width=0)
Index Cond: (history_id = 1000)
Planning time: 0.191 ms
(7 rows)

2017-04-17 17:28 GMT+08:00 Steven Chang <stevenchang1213(at)gmail(dot)com>:

> hello Samed YILDIRIM,
>
> Thanks for your reply, and constraint_exclusion is default --
> partitiion.
> Well ..... I see......
> It's just a term difference between postgres and oracle.
> The URL you posted makes me clear, thank you.
>
>
> BR,
> Steven
>
> 2017-04-17 16:53 GMT+08:00 Samed YILDIRIM <samed(at)reddoc(dot)net>:
>
>> Hi Steven,
>>
>> Bitmap index is created on heap for using multiple index by PostgreSQL.
>> It is not created by DBA like Oracle etc.
>>
>> https://www.postgresql.org/docs/9.4/static/indexes-bitmap-scans.html
>>
>> What is your constraint_exclusion parameter in your database? You can
>> check your parameter with following command.
>>
>> show constraint_exclusion;
>>
>> https://www.postgresql.org/docs/9.4/static/ddl-partitioning.html
>>
>> Best regards.
>>
>>
>> İyi çalışmalar.
>> Samed YILDIRIM
>>
>>
>>
>> 17.04.2017, 07:01, "Steven Chang" <stevenchang1213(at)gmail(dot)com>:
>>
>> Dear Sir,
>>
>> Here is my env.
>>
>> OS: jessie
>> Linux faiserver 3.16.0-4-amd64 #1 SMP Debian 3.16.39-1+deb8u2 (2017-03-07)
>> x86_64 GNU/Linux
>>
>> root(at)faiserver:~# apt show postgresql-9.4
>> Package: postgresql-9.4
>> Version: 9.4.10-0+deb8u1
>>
>> Postgres version : default bundle postgres package
>> PostgreSQL 9.4.10 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
>> 4.9.2-10) 4.9.2, 64-bit
>>
>> I am testing the range partition feature, here is my test scenario in
>> book "Postgresql Developer's Guide".
>>
>> Creating the master table :
>>
>> CREATE TABLE sales_record
>> (
>> id NUMERIC PRIMARY KEY,
>> sales_amount NUMERIC,
>> sales_date DATE NOT NULL DEFAULT CURRENT_DATE
>> );
>>
>> Creating a range partition table :
>>
>> CREATE TABLE sales_record_m1_to_m2
>> (
>> PRIMARY KEY (id, sales_date),
>> CHECK (sales_date >= DATE '2014-01-01'
>> AND sales_date < DATE '2014-03-01')
>> )
>> INHERITS (sales_record);
>>
>> CREATE TABLE sales_record_m3_to_m4
>> (
>> PRIMARY KEY (id, sales_date),
>> CHECK (sales_date >= DATE '2014-03-01'
>> AND sales_date < DATE '2014-05-01')
>> )
>> INHERITS (sales_record);
>>
>> CREATE TABLE sales_record_m5_to_m6
>> (
>> PRIMARY KEY (id, sales_date),
>> CHECK (sales_date >= DATE '2014-05-01'
>> AND sales_date < DATE '2014-07-01')
>> )
>> INHERITS (sales_record);
>>
>> CREATE TABLE sales_record_m7_to_m8
>> (
>> PRIMARY KEY (id, sales_date),
>> CHECK (sales_date >= DATE '2014-07-01'
>> AND sales_date < DATE '2014-09-01')
>> )
>> INHERITS (sales_record);
>>
>> CREATE TABLE sales_record_m9_to_m10
>> (
>> PRIMARY KEY (id, sales_date),
>> CHECK (sales_date >= DATE '2014-09-01'
>> AND sales_date < DATE '2014-11-01')
>> )
>> INHERITS (sales_record);
>>
>> CREATE TABLE sales_record_m11_to_m12
>> (
>> PRIMARY KEY (id, sales_date),
>> CHECK (sales_date >= DATE '2014-11-01'
>> AND sales_date < DATE '2015-01-01')
>> )
>> INHERITS (sales_record);
>>
>> Creating an index on child tables :
>> CREATE INDEX m1_to_m2_sales_date ON sales_record_m1_to_m2 (sales_date);
>> CREATE INDEX m3_to_m4_sales_date ON sales_record_m3_to_m4 (sales_date);
>> CREATE INDEX m5_to_m6_sales_date ON sales_record_m5_to_m6 (sales_date);
>> CREATE INDEX m7_to_m8_sales_date ON sales_record_m7_to_m8 (sales_date);
>> CREATE INDEX m9_to_m10_sales_date ON sales_record_m9_to_m10 (sales_date);
>> CREATE INDEX m11_to_m12_sales_date ON sales_record_m11_to_m12
>> (sales_date);
>>
>> Creating a trigger on the master table :
>> CREATE OR REPLACE FUNCTION sales_record_insert()
>> RETURNS TRIGGER AS $$
>> BEGIN
>> IF (NEW.sales_date >= DATE '2014-01-01' AND
>> NEW.sales_date < DATE '2014-03-01') THEN
>> INSERT INTO sales_record_m1_to_m2 VALUES (NEW.*);
>> ELSEIF (NEW.sales_date >= DATE '2014-03-01' AND
>> NEW.sales_date < DATE '2014-05-01') THEN
>> INSERT INTO sales_record_m3_to_m4 VALUES (NEW.*);
>> ELSEIF (NEW.sales_date >= DATE '2014-05-01' AND
>> NEW.sales_date < DATE '2014-07-01') THEN
>> INSERT INTO sales_record_m5_to_m6 VALUES (NEW.*);
>> ELSEIF (NEW.sales_date >= DATE '2014-07-01' AND
>> NEW.sales_date < DATE '2014-09-01') THEN
>> INSERT INTO sales_record_m7_to_m8 VALUES (NEW.*);
>> ELSEIF (NEW.sales_date >= DATE '2014-09-01' AND
>> NEW.sales_date < DATE '2014-11-01') THEN
>> INSERT INTO sales_record_m9_to_m10 VALUES (NEW.*);
>> ELSEIF (NEW.sales_date >= DATE '2014-11-01' AND
>> NEW.sales_date < DATE '2015-01-01') THEN
>> INSERT INTO sales_record_m11_to_m12 VALUES (NEW.*);
>> ELSE
>> RAISE EXCEPTION 'Date is out of range. Something is wrong with
>> sales_record_insert_trigger() function';
>> END IF;
>> RETURN NULL;
>> END;
>> $$
>> LANGUAGE plpgsql;
>>
>> CREATE TRIGGER sales_day_trigger
>> BEFORE INSERT ON sales_record
>> FOR EACH ROW
>> EXECUTE PROCEDURE sales_record_insert();
>>
>> Insert testing DATA :
>>
>> INSERT INTO sales_record (id, sales_amount, sales_date)
>> VALUES (1, 500, TO_DATE('02/12/2014','MM/DD/YYYY'));
>> INSERT INTO sales_record (id, sales_amount, sales_date)
>> VALUES (2, 1500, TO_DATE('03/10/2014','MM/DD/YYYY'));
>> INSERT INTO sales_record (id, sales_amount, sales_date)
>> VALUES (3, 2500, TO_DATE('05/15/2014','MM/DD/YYYY'));
>> INSERT INTO sales_record (id, sales_amount, sales_date)
>> VALUES (4, 2000, TO_DATE('07/25/2014','MM/DD/YYYY'));
>> INSERT INTO sales_record (id, sales_amount, sales_date)
>> VALUES (5, 2200, TO_DATE('09/15/2014','MM/DD/YYYY'));
>> INSERT INTO sales_record (id, sales_amount, sales_date)
>> VALUES (6, 1200, TO_DATE('11/15/2014','MM/DD/YYYY'));
>>
>> When I check the query plan with explain statement, I found "bitmap index
>> in use".
>> However , default is btree ndex created when issuing create index without
>> type.
>>
>> \d+ sales_record_m9_to_m10
>> Table "public.sales_record_m9_to_m10"
>> Column | Type | Modifiers | Storage
>> | Stats target | Description
>> --------------+---------+-----------------------------------
>> ---+---------+--------------+-------------
>> id | numeric | not null | main
>> | |
>> sales_amount | numeric | | main
>> | |
>> sales_date | date | not null default ('now'::text)::date | plain
>> | |
>> Indexes:
>> "sales_record_m9_to_m10_pkey" PRIMARY KEY, btree (id, sales_date)
>> "m9_to_m10_sales_date" btree (sales_date)
>> Check constraints:
>> "sales_record_m9_to_m10_sales_date_check" CHECK (sales_date >= '
>> 2014-09-01'::date AND sales_date < '2014-11-01'::date)
>> Inherits: sales_record
>>
>> explain select * from sales_record where sales_date='2014-9-13';
>> Bitmap Heap Scan on sales_record_m9_to_m10 (cost=4.18..12.64 rows=4
>> width=68)
>> Recheck Cond: (sales_date = '2014-09-13'::date)
>> -> Bitmap Index Scan on m9_to_m10_sales_date (cost=0.00..4.18 rows=4
>> width=0)
>> Index Cond: (sales_date = '2014-09-13'::date)
>> To my understanding with other RDBMS, bitmap index is a type of index
>> other than b-tree ones.
>> Could anyone knows about the index internal of postgresql help me
>> understand it ?
>> Thank you.
>>
>> Best Regards,
>> Steven
>>
>>
>