CODEWITHSUNDEEP
postgresqlindexingwhere-clauseequality
zlatko
zlatko

Reputation: 650

Best Postgresql index type to be used with not equal (<>) where condition

I need to query two identical huge tables (more than milion records). Besides other conditions, there is also a not equal where condition on a columns pair of varchar type.

Is standard btree type index suitable or some other index type would be more suitable for not equal (<>) where condition?

Upvotes: 1

Views: 1229

Answers (2)

Jawad Ahmad
Jawad Ahmad

Reputation: 153

Heroku dev center guide seem to have good explanation on Postgres indexes. I personally use B-Tree indexes as they are transaction safe unlike Hash indexes. Please take a look.

"B-Tree is the default that you get when you do CREATE INDEX. Virtually all databases will have some B-tree indexes. B-trees attempt to remain balanced, with the amount of data in each branch of the tree being roughly the same. Therefore the number of levels that must be traversed to find rows is always in the same ballpark."

More details here : Heroku Dev Center

Upvotes: 0

Chase
Chase

Reputation: 3105

Why not try it with different indexes and let Postgres tell you which was most effective with the EXPLAIN command? Unless your data contains some sort of rare edge case which breaks indexing functionality, you can test it for real and see the actual results without having to guess.

Upvotes: 1

Related Questions