Postgresql partial index exceeds maximum index row size

Question

I am trying to create a partial index to speed up full text search in my postgresql database.

CREATE INDEX book_search_not_null_index ON books(description) WHERE description IS NOT NULL;

I am getting the following error when trying to create my index:

index row requires 16016 bytes, maximum size is 8191

Some of the descriptions are quite long and are exceeding the row limit for my index. All I want from my index though is to make sure that I am not considering books with no description in my search (roughly 10% of the descriptions are null). Is there any way around this restriction or a better way to structure this index?

Clodoaldo Neto · Accepted Answer · 2017-01-23 18:21:42Z

3

Use GIN indexes for full text search

create index book_search_not_null_index ON books 
using GIN (to_tsvector('english', description))
where description is not null

edited Jan 23, 2017 at 18:21

answered Jan 23, 2017 at 17:02

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Over a year ago

Thanks for the reply, I'm actually using an index for the tsvector right now, but the tsvector is null if the description is, and I would like to exclude those rows from the search. Is there a good way to do that?

Clodoaldo Neto Over a year ago

@user1023465: Edited where...

Collectives™ on Stack Overflow

Postgresql partial index exceeds maximum index row size

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related