Re: [ADMIN] Bitmap Index Scan when btree index created - Mailing list pgsql-admin
From | Samed YILDIRIM |
---|---|
Subject | Re: [ADMIN] Bitmap Index Scan when btree index created |
Date | |
Msg-id | 8181551492419218@web17o.yandex.ru Whole thread Raw |
In response to | [ADMIN] Bitmap Index Scan when btree index created (Steven Chang <stevenchang1213@gmail.com>) |
Responses |
Re: [ADMIN] Bitmap Index Scan when btree index created
|
List | pgsql-admin |
Hi Steven,
Bitmap index is created on heap for using multiple index by PostgreSQL. It is not created by DBA like Oracle etc.
What is your constraint_exclusion parameter in your database? You can check your parameter with following command.
show constraint_exclusion;
Best regards.
İyi çalışmalar.
Samed YILDIRIM
17.04.2017, 07:01, "Steven Chang" <stevenchang1213@gmail.com>:
Dear Sir,Here is my env.OS: jessieLinux faiserver 3.16.0-4-amd64 #1 SMP Debian 3.16.39-1+deb8u2 (2017-03-07) x86_64 GNU/Linuxroot@faiserver:~# apt show postgresql-9.4Package: postgresql-9.4Version: 9.4.10-0+deb8u1Postgres version : default bundle postgres packagePostgreSQL 9.4.10 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bitI 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 $$BEGINIF (NEW.sales_date >= DATE '2014-01-01' ANDNEW.sales_date < DATE '2014-03-01') THENINSERT INTO sales_record_m1_to_m2 VALUES (NEW.*);ELSEIF (NEW.sales_date >= DATE '2014-03-01' ANDNEW.sales_date < DATE '2014-05-01') THENINSERT INTO sales_record_m3_to_m4 VALUES (NEW.*);ELSEIF (NEW.sales_date >= DATE '2014-05-01' ANDNEW.sales_date < DATE '2014-07-01') THENINSERT INTO sales_record_m5_to_m6 VALUES (NEW.*);ELSEIF (NEW.sales_date >= DATE '2014-07-01' ANDNEW.sales_date < DATE '2014-09-01') THENINSERT INTO sales_record_m7_to_m8 VALUES (NEW.*);ELSEIF (NEW.sales_date >= DATE '2014-09-01' ANDNEW.sales_date < DATE '2014-11-01') THENINSERT INTO sales_record_m9_to_m10 VALUES (NEW.*);ELSEIF (NEW.sales_date >= DATE '2014-11-01' ANDNEW.sales_date < DATE '2015-01-01') THENINSERT INTO sales_record_m11_to_m12 VALUES (NEW.*);ELSERAISE EXCEPTION 'Date is out of range. Something is wrong withsales_record_insert_trigger() function';END IF;RETURN NULL;END;$$LANGUAGE plpgsql;CREATE TRIGGER sales_day_triggerBEFORE INSERT ON sales_recordFOR EACH ROWEXECUTE 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_m10Table "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_recordexplain 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
pgsql-admin by date: