This article is half-done without your Comment! *** Please share your thoughts via Comment ***
If you are searching about, What is Expression Index, this is the one of the right article for you.
We know about the different types of PostgreSQL Index.
If you don’t about this, please visit below few links.
Most of the Database Administrators or Developers are doing a common mistake,
When you create any Index on Table Column, dose not mean that you can use the Indexed column in any expression and Index will work as per the expectation.
When you used Indexed column in any kind of expression, query planner simply skips the scanning of Indexes of that column.
For example,
We have created one index on Date column and in WHERE are extracting days from this Date column.
In this situation, the Index will not work on Date column and for such specific requirement, we have to create the Expression Index of PostgreSQL.
Let me demonstrate this.
First create one sample table:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_ItemTransactions ( TranID SERIAL ,TransactionDate TIMESTAMPTZ ,TransactionName TEXT ); |
Generate sample data for testing the performance of Indexes:
|
1 2 3 4 |
INSERT INTO tbl_ItemTransactions (TransactionDate, TransactionName) SELECT x, 'dbrnd' FROM generate_series('2015-01-01 00:00:00'::timestamptz, '2016-08-01 00:00:00'::timestamptz,'2 seconds'::interval) a(x); |
Total inserted record count is 24969601:
|
1 |
SELECT COUNT(1) FROM tbl_ItemTransactions; |
Now create index on TransactionDate column:
|
1 2 |
CREATE INDEX idx_tbl_ItemTransactions_TransactionDate ON tbl_ItemTransactions (TransactionDate); |
Lets see the plan of query with date filter:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
EXPLAIN ANALYZE SELECT *FROM tbl_ItemTransactions WHERE TransactionDate BETWEEN '20150808' AND '20160108'; /* "Index Scan using idx_tbl_itemtransactions_transactiondate on tbl_itemtransactions (cost=0.44..249472.54 rows=6681405 width=18) (actual time=61.558..17765.979 rows=6609601 loops=1)" "Index Cond: ((transactiondate >= '2015-08-08 00:00:00+05:30'::timestamp with time zone) AND (transactiondate <= '2016-01-08 00:00:00+05:30'::timestamp with time zone))" "Planning time: 87.751 ms" "Execution time: 29691.279 ms" */ |
Check Index usage by above query
(Result is one index scan):
|
1 2 3 |
SELECT * FROM pg_stat_user_indexes WHERE indexrelname='idx_tbl_itemtransactions_transactiondate'; |
Lets see the plan of query with date function filter:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
EXPLAIN ANALYZE SELECT *FROM tbl_ItemTransactions WHERE EXTRACT(day FROM TransactionDate) = 8; /* "Seq Scan on tbl_itemtransactions (cost=0.00..533587.00 rows=124848 width=18) (actual time=1246.093..37028.883 rows=820800 loops=1)" " Filter: (date_part('day'::text, transactiondate) = '8'::double precision)" " Rows Removed by Filter: 24148801" "Planning time: 0.396 ms" "Execution time: 58230.847 ms" */ |
Check Index usage by above query:
The Result is one index scan, means above query processed without scaning any index.
We created one index on TransactionDate, but when we use this column with any of default function, planner skips the index fot that column.
|
1 2 3 |
SELECT * FROM pg_stat_user_indexes WHERE indexrelname='idx_tbl_itemtransactions_transactiondate'; |
Now we should create one expression Index:
Please provide your require timezone otherwise It produces error like:
|
1 |
ERROR: functions in index expression must be marked IMMUTABLE |
|
1 2 |
CREATE INDEX idx_tbl_ItemTransactions_TransactionDate_day ON tbl_ItemTransactions ((EXTRACT(day FROM TransactionDate AT TIME ZONE 'UTC'))); |
Lets execute same query with date function filter:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
EXPLAIN ANALYZE SELECT *FROM tbl_ItemTransactions WHERE EXTRACT(day FROM TransactionDate AT TIME ZONE 'UTC') = 8; /* "Bitmap Heap Scan on tbl_itemtransactions (cost=2340.01..160893.01 rows=124848 width=18) (actual time=367.653..2181.489 rows=820800 loops=1)" " Recheck Cond: (date_part('day'::text, (transactiondate)::timestamp without time zone) = '8'::double precision)" " Heap Blocks: exact=5248" " -> Bitmap Index Scan on idx_tbl_itemtransactions_transactiondate_day (cost=0.00..2308.80 rows=124848 width=0) (actual time=365.386..365.386 rows=820800 loops=1)" " Index Cond: (date_part('day'::text, (transactiondate)::timestamp without time zone) = '8'::double precision)" "Planning time: 0.297 ms" "Execution time: 3608.713 ms" */ |
Check created new Expression Index usage by above query
(Result is one index scan):
|
1 2 3 |
SELECT * FROM pg_stat_user_indexes WHERE indexrelname='idx_tbl_itemtransactions_transactiondate_day'; |
