Re: Bitmap Index Scan when btree index created

Steven Chang <stevenchang1213@xxxxxxxxx> · Mon, 17 Apr 2017 17:28:15 +0800

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@xxxxxxxxxx>:
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@xxxxxxxxx>:
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@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