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

Ajay Patel · May 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...

How to make queries faster with multicolumn indexes

Ranjeet Dhumal · March 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 Chiu · December 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 Dhumal · December 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 Yen · November 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 Momjian · November 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 foreign data sources, like Nosql stores and other Postgres servers.

Query Plan Improvement with Expression Indexes in PostgreSQL

Bruce Momjian · November 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. Not only do expression indexes allow rapid lookups of matching expressions, but they also provide optimizer statistics, which improve row estimates and hence query plans.

How to improve Row Estimates with Expression Indexes in Postgres

Bruce Momjian · November 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. I plan to do so in this blog post.

Are Hash Indexes Faster than Btree Indexes in Postgres?

Amit Kapila · November 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 compared to Btree indexes.

Indexing Documents for Full Text Search in PostgreSQL

Dave Page · November 6, 2019

During a conversation with $BOSS a couple of days ago the topic of the new Full Text Search in MongoDB came up. He (knowing of, but presumably having never worked with FTS in PostgreSQL) suggested I might create a tool like the one the MongoDB guys were proudly showing off in their keynote video from their recent conference to make it easy to index and search HTML documents on disk.

Parallel index-only scans in PostgreSQL

Rafia S · July 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

Rafia Sabih · July 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...

Parallel Index Scans in PostgreSQL

EDB Team · June 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)...

Materialized Views and Foreign Data Wrappers

Bruce Momjian · October 31, 2017

Foreign data wrappers allow data to be pulled from foreign data sources, like Nosql stores and other Postgres servers.

PostgreSQL's Hash Indexes Are Now Cool

Robert Haas · September 26, 2017

The problem with hash indexes wasn't simply that nobody had bothered to write the code for write-ahead logging, but that the code was not structured in a way that made it possible to add write-ahead logging that would actually work correctly

Creating Descending Indexes

Bruce Momjian · April 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...

Index Order Does Matter

Bruce Momjian · April 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...

Expression Index Statistics and Joins

Bruce Momjian · April 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...

PostgreSQL Indexes: Hash Indexes are Faster than Btree Indexes?

EDB Team · March 24, 2017

There are multiple ways in which we can compare the performance of Hash and Btree PostgreSQL index types, like the time taken for index creation, search, or insertion in the index. This blog will mainly focus on the search operation

Statistics on Expression Indexes

Bruce Momjian · March 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...

Get Postgres tips & tricks

Subscribe to our newsletter to get advanced Postgres how-tos.

Related Tutorials

Featured Webinar

Programming the SQL Way with Common Table Expression

This webinar will compare jquery and SQL/JSON by discussing: The similarities and differences between the two features, the equivalent functionality where they aren't identical, and examples of how they compare in practice

Watch Now

Introducing Postgres Pulse Live

Free To Attend: Every other Monday 11AM ET

A biweekly blog and live video forum series built to offer insight into how we analyze problems and develop solutions using Postgres.

About Postgres Pulse Live

6 Things a Developer Should Know About Postgres

In this tech guide, we describe the six things a developer should know about in terms of how easy it is to install, work with, and deploy Postgres as well as the unique capabilities and wide range of features that Postgres offers.

Download Now