Re: [ADMIN] Bitmap Index Scan when btree index created

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