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