Query Performance Comparison
Let’s compare query performance with and without indexes:
Query without index
-- Query without index
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;
Output:
Seq Scan on orders (cost=0.00..31.75 rows=10 width=16) (actual time=0.013..0.015 rows=2 loops=1)
Filter: (customer_id = 1)
Rows Removed by Filter: 2
Planning Time: 0.047 ms
Execution Time: 0.029 ms
(5 rows)
Query with index
-- Query with index
CREATE INDEX ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;
Output:
Bitmap Heap Scan from orders (cost=4.22..8.24 rows=1 width=16) (actual time=0.033..0.034 rows=2 loops=1)
Recheck Cond: ( id_customer = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on orders_customer_id_idx (cost=0.00..4.22 rows=1 width=0) (actual time=0.024..0.024 rows=2 loops=1)
Index Cond: Where (customer_id = 1),
Planning Time: 0.124 ms
Execution Time: 0.050 ms
(6 rows)
Foreign Key Indexing and Performance in PostgreSQL
As the world of databases is always changing, PostgreSQL is one of the autonomous options because of its dependability, capability to handle huge amounts of data, and fast performance. Effective indexing, especially for foreign keys, is an important key for enhancing the speed of PostgreSQL.
Appropriate indexes for foreign keys can greatly speed up queries, protect database integrity, and improve the performance and efficiency of the database. Through this article, we will learn how to optimize your PostgreSQL database with foreign key indexing.