Category: Indexing

Hans-Jürgen Schönig builds some indexes on vector data:

In the previous post we have imported a fairly large data set containing Wikipedia data, which we downloaded using pgai. However, importing all this data is not enough because we also need to keep an eye on efficiency. Therefore, it is important to understand that indexing is the key to success.

Click through for an overview of what options are available for indexing vectors in PostgreSQL, as well as the trade-offs and disk space requirements.

Comments closed

Lukas Fittl compares and contrasts:

When it comes to optimizing query performance, indexing is one of the most powerful tools available to database engineers. Both PostgreSQL and Microsoft SQL Server (or Azure SQL) use B-Tree indexes as their default indexing structure, but the way each system implements, maintains, and uses those indexes varies in subtle but important ways.

In this blog post, we explore key areas where PostgreSQL and SQL Server diverge: how their B-Tree indexes implementations behave under the hood and how they store and access data on disk. We’ll also benchmark the impact of deduplication of values on index size in each database system.

I love this kind of post because you hear that SQL Server has indexes and PostgreSQL has indexes (or Oracle has indexes or whatever), and thus, all of your index building knowledge in one applies to the other…right?

One thing that changes the article a bit is that the author doesn’t use page-level compression on indexes in SQL Server. I’d expect the results to change a fair amount, even if the SQL Server non-clustered indexes still ended up larger in the end than PostgreSQL indexes.

Comments closed

Lee Asher shows off a neat form of indexing:

In Parts I (Solving the Overlap Query Problem in PostgreSQL) and II (Overlapping Ranges in Subsets in PostgreSQL) of this series, we used the GiST index type – and its lesser known cousin: SP-GiST – to turbocharge the performance of overlap queries. But GiST indexes are extremely versatile, with uses far beyond the examples I have used so far.

In this final article, we’ll explore some of the many other ways they can be used.

Click through for use cases, such as multi-dimensional BETWEEN operations (e.g., bid price between X and Y, and ask price between X1 and Y1), date intervals, and more.

Comments closed

Chad Callihan performs a text search:

When it comes to MySQL queries against columns containing text, FULLTEXT indexes offer a variety of approaches that go beyond your basic pattern matching. When creating a FULLTEXT index for querying text data, we can use MATCH and AGAINST along with our choice of three modes.

Click through for the three modes, as well as examples of queries for each.

Comments closed

Elizabeth Christensen adds an index:

Materialized views are widely used in Postgres today. Many of us are working with using connected systems through foreign data wrappers, separate analytics systems like data warehouses, and merging data from different locations with Postgres queries. Materialized views let you precompile a query or partial table, for both local and remote data. Materialized views are static and have to be refreshed.

One of the things that can be really important for using materialized views efficiently is indexing.

Adding indexes to Postgres in general is critical for operation and query performance. Adding indexes for materialized views is also generally recommended for a few different reasons.

Click through for those reasons. This article illustrates some of the differences between indexed views in SQL Server and materialized views in PostgreSQL.

Comments closed

Mehdi Ghapanvari wants us to think of the poor, downtrodden non-clustered indexes:

Do you wonder why SQL Server ignores an index? Do you think about how you can deal with this problem? When you include too many columns in a query you decrease index selectivity. So, if you write a query like “Select * From …” the chance of choosing your nonclustered index will decrease.

The article is generally sound, though not all-inclusive. If you really want to use an index, you can use index hints. I personally have no qualms about using them, though hints are telling the optimizer that you know better than it, so be really sure you do actually know better than it before shipping that off.

Comments closed