Re: Bitmap Index Scan when btree index created

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
>
>