Comparing indexed scans and sequential scans

In this recipe, let's compare the index and sequential scan behaviors using the inotifywait utility command, which will print a message when the mentioned event occurs on the given files.

Getting ready

inotify tools is a module that we can download using either the apt-get or yum command in the corresponding Linux distribution. This contrib module is developed based on the inotify kernel API, which provides some kind of audit mechanism over the filesystem. To compare the index and sequential scan behavior, let's audit the relation and index physical file while executing the SQL queries.

How to do it...

Let's get the locations of index and relation physical files location using the pg_relation_filepath function, as follows:

benchmarksql=# SELECT pg_relation_filepath('pric_idx'); 
pg_relation_filepath  
---------------------- 
base/12439/16545 
(1 row) 
 
benchmarksql=# SELECT pg_relation_filepath('bmsql_item&apos...