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

Поиск
Список
Период
Сортировка
От Steven Chang
Тема Re: [ADMIN] Bitmap Index Scan when btree index created
Дата
Msg-id CAEJt7k0HDL33u7sQJeCHY2etw-sROsw+B1JQ8keroCQgxOudWA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [ADMIN] Bitmap Index Scan when btree index created  (Steven Chang <stevenchang1213@gmail.com>)
Список pgsql-admin
Дерево обсуждения
Share with you.

Access methods (sequential scan, index scan, bitmap scan, index-only scan).

1.Sequential scans
The sequential scan scans the whole table sequentially to retrieve the required rows
from the table. The planner selects the sequential scan when a query is retrieving
large number of rows from the table and the number of appropriate indexes found.
The following is an example of a sequential scan where a query has to select all
the history table's records where hist_id is greater than 1000. There is an index
defined for the hist_id column but it won't help in this case:
warehouse_db=# EXPLAIN SELECT * FROM record.history WHERE
history_id > 1000;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on history (cost=0.00..2184580.00 rows=99998968
width=46)
Filter: (history_id > 1000)
Planning time: 57.910 ms
(3 rows)

2.Index scans
An index is a way to efficiently retrieve specific rows from database. The planner
chooses an index scan if any index satisfies the WHERE condition. It is faster than
the normal table scan because it does not traverse the whole set of column of rows.
Normally, an index is created on tables with lesser number of columns. In index
scans, PostgreSQL picks only one tuple's pointer and accesses the tuple/row from
the table.
An index based on all columns of table has no performance benefit.
Here is an example of an index scan:
warehouse_db=# EXPLAIN SELECT * FROM record.history WHERE
history_id=1000;
QUERY PLAN
------------------------------------------------------------------
Index Scan using idx on history (cost=0.57..8.59 rows=1
width=46)
Index Cond: (history_id = 1000)
Planning time: 0.142 ms
(3 rows)

3.Index-only scans
If all the columns of a query are part of the index, then the planner selects index-only
scans. In this case, the tuple on the page is visible, so tuples are picked from an index
instead of a heap, which is a really big performance boost. The following example
shows an index-only scan:
warehouse_db=# EXPLAIN SELECT history_id FROM record.history WHERE
history_id = 1000;
QUERY PLAN
------------------------------------------------------------------
Index Only Scan using idx on history (cost=0.57..8.59 rows=1
width=4)
Index Cond: (history_id = 1000)
Planning time: 0.121 ms
(3 rows)
In the preceding example, all the columns in the target and qual lists are part of the
index; therefore, the planner selects index-only scans.

4.Bitmap scans
Unlike the index scan, the bitmap scan fetches all the tuple-pointers from the disks
and fetches the tuple using the bitmap data structure. A bitmap scan is useful only
when small numbers of rows are needed. Here is an example of a bitmap scan in
which we get rows that have hist_id as 1000 or 20000:
warehouse_db=# EXPLAIN SELECT * FROM record.history WHERE
history_id = 1000 AND history_id = 20000;
QUERY PLAN
------------------------------------------------------------------
Result (cost=4.58..8.59 rows=1 width=46)
One-Time Filter: false
-> Bitmap Heap Scan on history (cost=4.58..8.59 rows=1
width=46)
Recheck Cond: (history_id = 1000)
-> Bitmap Index Scan on idx (cost=0.00..4.58 rows=1
width=0)
Index Cond: (history_id = 1000)
Planning time: 0.191 ms
(7 rows)

2017-04-17 17:28 GMT+08:00 Steven Chang <stevenchang1213@gmail.com>:
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@reddoc.net>:
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: 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



В списке pgsql-admin по дате отправления:

Предыдущее
От: Jerry Sievers
Дата:
Сообщение: Re: [ADMIN] Question about pg_xlog
Следующее
От: Sumeet Shukla
Дата:
Сообщение: [ADMIN] List all users with read write access on a PG server with multipledatabase and objects