{"id":2193,"date":"2025-02-19T15:21:13","date_gmt":"2025-02-19T08:21:13","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2193"},"modified":"2025-03-07T10:45:42","modified_gmt":"2025-03-07T03:45:42","slug":"postgresql-index","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-index\/","title":{"rendered":"PostgreSQL Index"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use PostgreSQL Index to increase the query performance.<\/p>\n\n\n\n

Where PostgreSQL stores the data #<\/a><\/h2>\n\n\n\n

First, connect to the inventory<\/code> database on the local PostgreSQL using psql<\/code>:<\/p>\n\n\n

psql -U postgres -d inventory<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll prompt you to enter a password for the user postgres<\/code>.<\/p>\n\n\n\n
Second, show the location of the data directory using the following SHOW<\/code> command:<\/p>\n\n\n
SHOW<\/span> data_directory;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you use windows, you’ll see the output like this:<\/p>\n\n\n
           data_directory\n-------------------------------------<\/span>\n C:\/Program Files\/PostgreSQL\/17<\/span>\/data<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you use macOS or Linux, you’ll see a different path. Additionally, if you don’t use the PostgreSQL 17, you’ll get the different version number (17) in the returned path.<\/p>\n\n\n\n
The data directory is a directory that PostgreSQL stores database files, configurations, and logs.<\/p>\n\n\n\n
Third, view the directories of the base<\/code> directory:<\/p>\n\n\n
C:\/Program Files\/PostgreSQL\/17<\/span>\/data\/base\/<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
There will be directories whose names are numbers. Each directory represents a database in your PostgreSQL server.<\/p>\n\n\n\n
Fourth, retrieve the oid<\/code> and database name:<\/p>\n\n\n
SELECT<\/span>\n  oid<\/span>,\n  datname\nFROM<\/span>\n  pg_database;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
  oid<\/span>  |    datname\n-------+----------------<\/span>\n     5<\/span> | postgres\n     1<\/span> | template1\n     4<\/span> | template0\n 46769<\/span> | inventory\n...<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
An oid<\/code> (object identifier) is a unique identifier that PostgreSQL assigns to a database object, such as a database or a table.<\/p>\n\n\n\n
The oid 46769<\/code> corresponds to the directory that stores the data of the inventory<\/code> database.<\/p>\n\n\n\n
Fifth, find the oid<\/code> of the tables in the inventory<\/code> database:<\/p>\n\n\n
SELECT<\/span>\n  c.oid,\n  c.relname\nFROM<\/span>\n  pg_class c\n  JOIN<\/span> pg_namespace n ON<\/span> n.oid = c.relnamespace\nWHERE<\/span>\n  c.relkind = 'r'<\/span>\n  AND<\/span> n.nspname NOT<\/span> IN<\/span> ('pg_catalog'<\/span>, 'information_schema'<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
  oid<\/span>  |      relname\n-------+-------------------<\/span>\n 46908<\/span> | users\n 46917<\/span> | profiles\n 46936<\/span> | categories\n 46928<\/span> | brands\n 46949<\/span> | products\n 46977<\/span> | product_tags\n 46970<\/span> | tags\n 46999<\/span> | inventories\n 46993<\/span> | warehouses\n 47022<\/span> | transactions\n 47429<\/span> | product_groups\u00a0\u00a0<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
How PostgreSQL Stores the data #<\/a><\/h2>\n\n\n\n
PostgreSQL stores data of a table using a heap file storage model. Each table corresponds to a heap file<\/strong>.<\/p>\n\n\n\n
For example, PostgreSQL stores the data of the products<\/code> table in a heap file with the name 46949<\/code>. You can find the files in the database directory:<\/p>\n\n\n
C:\/Program Files\/PostgreSQL\/17<\/span>\/data\/base\/46949<\/span>\/<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Each heap file consists of blocks<\/strong> (or pages<\/strong>). Typically, a block has a size of 8KB by default. Each block contains multiple tuples<\/strong> (or rows).<\/p>\n\n\n\n
Additionally, PostgreSQL maintains other information that determines which rows are accessible.<\/p>\n\n\n\n
Full table scan #<\/a><\/h2>\n\n\n\n
When retrieving data from a table, PostgreSQL has to read data from the heap file and load it to the memory for filtering the rows.<\/p>\n\n\n\n
A full table scan means that PostgreSQL must read all rows from a heap file (table) to memory and search for rows.<\/p>\n\n\n\n
Most of the time, a full table scan is not efficient because PostgreSQL must read every block, even if only a few rows match the condition.<\/p>\n\n\n\n
Introduction to PostgreSQL Index #<\/a><\/h2>\n\n\n\n
An index is a separate data structure that allows PostgreSQL to locate rows quickly without scanning the table.<\/p>\n\n\n\n
An index is similar to an index in a book, which allows you to find information quickly without reading every page.<\/p>\n\n\n\n
Here’s how you would create an index:<\/p>\n\n\n\n