O'Reilly logo

PostgreSQL High Performance Cookbook by Dinesh Kumar, Chitij Chauhan

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Index lookup

In this recipe, we will be discussing various index scans, and how they try to fetch matching tuples from tables.

Getting ready

As we all know, an index is a physical object that can help a query retrieve table data much faster than a sequential scan. In the explain plan, we frequently get index scan, bitmap heap scan, or index only scan node types, which works on a particular index. Whenever an index is scanned, then an immediately followed action would be to return the matching tuples from the index, build a bitmap in memory, or fetch the tuples from the base table.

How to do it...

To demonstrate the index scan, let's run the following simple query and see how many tuples it is reading from the base table:

benchmarksql=# SELECT pg_stat_reset(); ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required