PostgreSQL Index type

Highlighted
Level 2 Adventurer

PostgreSQL Index type

Hi,

 

I am moving my DB from Oracle to Postgres.

In one of the query I encountered that it is using ctxsys indextype. 

I want to know what ctxsys actually is and is there any solutions in Postgres ?

Archived Discussions


Effective March 31st, we will no longer engage on PostgresRocks.


How to engage with us further?


  • Thought Leadership: EDB Blogs

  • Tips and Tricks: Postgres Tutorials

  • Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket

  • Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.

3 REPLIES 3
Highlighted
EDB Team Member

Re: PostgreSQL Index type

Hi @neiljo10,

 

I searched for ctxsys index types on Oracle, it seems they are used for the full text search.

 

In Postgres, you have two index types (GIN and GiST) which are available in the postgres. Please refer below link for the reference : 

 

https://www.postgresql.org/docs/11/textsearch-indexes.html

 

Regards,

Sudhir

Highlighted
Level 2 Adventurer

Re: PostgreSQL Index type

Hi @slonkar 

 

We are creating an index for a BYTEA column.

 

When asked the same question on stack exchange, one person emphasized on the fact that an LOB column cannot be indexed.

 

Is it true ?

Highlighted
Level 3 Adventurer

Re: PostgreSQL Index type

Hi,

 

For bytea column we can create brin, btree index.

For using gin, gist index we have to create btree_gin and btree_gist contrib module.

 

Please find below test cases:

 

BRIN:

postgres=# create table test_brin (test bytea);

CREATE TABLE

postgres=# create index idx_brin on test_brin using brin (test);

CREATE INDEX

 

GIN:

postgres=# create extension btree_gin;

CREATE EXTENSION

postgres=# create table test_gin (test bytea);

CREATE TABLE

postgres=# create index idx_gin on test_gin using gin(test);

CREATE INDEX

 

GIST:

postgres=# create extension btree_gist;

CREATE EXTENSION

postgres=# create table test_gist (test bytea);

CREATE TABLE

postgres=# create index idx_gist on test_gist using gist(test);

CREATE INDEX

 

 

Hope this answers your question.

© 2019 EnterpriseDB Corporation. All rights reserved.   |   Privacy Policy   |  Terms of Use   |   Trademarks