Skip to content
Announcing BigAnimal: Fully managed PostgreSQL in the Cloud
Contact usDocsPlans

PostgreSQL Views and Indexes Tutorials and Examples

PostgreSQL provides many index types: B-tree, GiSTm GINm, Hash, and SP-GiST. Every Index sort uses a special algorithmic rule that's best suited to differing kinds of queries. PostgreSQL view is a logical table representing data of one or more tables through a SELECT statement. This Archive consists of tutorials and tricks to work with PostgreSQL Views and Indexes.

Index Corruption in PostgreSQL: The Hidden Cost of Your Queries

EDB Team5/7/2020

   A corruption in the database base often manifests itself in the form of errors when performing a DDL or DML operation on affected relations. The errors often show up as errors reading tables due to a missing file—that is rather straightforward, of ...

How to make queries faster with multicolumn indexes

Ranjeet Dhumal3/25/2020

This article introduces multicolumn indexes and explains how they can by used to make large queries run faster.

How to create, modify, delete, and manage views in PostgreSQL

Jackie Chiu12/17/2019

This article discusses VIEW in PostgreSQL. It describes how a VIEW functions as a shortcut if calling the same query multiple times, then defines the following commands: CREATE VIEW, ALTER VIEW, and DROP VIEW.

An Overview of PostgreSQL Indexes

Ranjeet Dhumal12/13/2019

This article describes indexes in PostgreSQL and how they can help retrieve data faster.

How to create and refresh data for materialized views in PostgreSQL

Richard Yen11/20/2019

Most relational database systems provide the functionality to create a VIEW, which basically acts like a shortcut or macro.

Using Materialized Views and Foreign Data Wrappers Together

Bruce Momjian11/7/2019

You might know that Postgres supports materialized views and foreign data wrappers (fdw). Briefly, materialized views allow for queries to be materialized and refreshed on demand. Foreign data wrappers allow data to be pulled from ...

Query Plan Improvement with Expression Indexes in PostgreSQL

Bruce Momjian11/7/2019

Most people know that Postgres allows the creation of indexes on expressions. This is helpful if you need index lookups of expressions used in where clauses. However, there is another benefit to expression indexes, and that is optimizer statistics. N ...

How to improve Row Estimates with Expression Indexes in Postgres

Bruce Momjian11/7/2019

In my previous blog post, I showed how statistics generated on expression indexes can be used to produce more accurate row counts and potentially better plans. While I did show more accurate row counts via explain, I did not show changed query plans. ...

Are Hash Indexes Faster than Btree Indexes in Postgres?

Amit Kapila11/7/2019

PostgreSQL have supported Hash Index for a long time, but they are not much used in production mainly because they are not durable. Now, with the next version of PostgreSQL, they will be durable. The immediate question is how do they perform as compa ...

Parallel index-only scans in PostgreSQL

Rafia S7/31/2018

In the previous blog, we saw that parallel index scans leads to significantly improves the performance of quite a few TPC-H queries. It is customary to analyze if its sister operator, namely index-only scan will benefit similarly when parallelized.

Performance of parallel index scans in PostgreSQL

EDB Team7/24/2018

This blog will continue the discussion of parallel query in PostgreSQL. In the previous blog of this series, we learned about parallel index scans, its design in PostgreSQL and the performance improvement achieved for a few queries on the industrial ...

Parallel Index Scans in PostgreSQL

EDB Team6/12/2018

There is a lot to say about parallelism in PostgreSQL. We have come a long way since I wrote my first post on this topic (Parallel Sequential Scans). Each of the past three releases (including PG-11, which is in its beta) have a parallel query as a m ...

Materialized Views and Foreign Data Wrappers

Bruce Momjian10/31/2017

*/ You might know that Postgres supports materialized views and foreign data wrappers (fdw). Briefly, materialized views allow for queries to be materialized and refreshed on demand. Foreign data wrappers allow data to be pulled from foreign data so ...

PostgreSQL's Hash Indexes Are Now Cool

Robert M. Haas9/26/2017

*/ Since I just committed the last pending patch to improve hash indexes to PostgreSQL 11, and since most of the improvements to hash indexes were committed to PostgreSQL 10 which is expected to be released next week, it seems like a good time for a ...

Creating Descending Indexes

Bruce Momjian4/18/2017

You might have noticed that create index allows you to specify asc or desc for each column being indexed, and you might have wondered why. Asc is the default, so there is no need to use it. Desc actually does have a use, but it isn't obvious. Postgr ...

Index Order Does Matter

Bruce Momjian4/12/2017

Postgres has supported multi-column indexes since 1997, e.g. create index i_test on test (a, b, c). It can easily use an index if the supplied columns are all at the front of the index, e.g. a and b in the previous index, but it can also use the inde ...

Expression Index Statistics and Joins

Bruce Momjian4/5/2017

In my previous blog post, I showed how statistics generated on expression indexes can be used to produce more accurate row counts, and potentially better plans. While I did show more accurate row counts via explain, I did not show changed query plans ...

PostgreSQL Indexes: Hash Indexes are Faster than Btree Indexes?

EDB Team3/24/2017

*/ PostgreSQL have supported Hash Index for a long time, but they are not much used in production mainly because they are not durable. Now, with the next version of PostgreSQL, they will be durable. The immediate question is how do they perform as c ...

Statistics on Expression Indexes

Bruce Momjian3/22/2017

Most people know that Postgres allows the creation of indexes on expressions. This is helpful if you need index lookups of expressions used in where clauses. However, there is another benefit to expression indexes, and that is optimizer statistics. N ...