From: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Cannot get query to use btree-gin index when ORDER BY
Date: Thu, 9 Apr 2015 23:39:26 +0200 (CEST)
Message-ID: <VisenaEmail.63.beee75c83c50054c.14ca0154fe9@tc7-visena> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi all. Using PG-9.4 I have a query which goes something like this The
simplified schema is like this: CREATE TABLE message( id serial PRIMARY KEY,
subject varchar NOT NULL, folder_id integer NOT NULL REFERENCES folder(id),
received timestamp not null, fts_all tsvector ); create index
message_fts_all_folder_idx ON message using gin (fts_all, folder_id); SELECT
m.id, m.subject FROM message m WHERE m.folder_id = 1 AND m.fts_all @@
to_tsquery('simple', 'foo') ORDER BY received DESC LIMIT 10; ... On my
dataset it uses an index I have on (folder_id, received DESC), then filters the
result, which is not optimal when searching in > 1million messages and the
result is large and I'm only after the first (newest) 10. What I'd like is to
have an index like this: create index message_fts_all_folder_idx ON message
using gin (fts_all, folder_id, received DESC); but GIN doesn't allow ASC/DESC
modifiers. Any hints on how to optimize this? Thanks. -- Andreas Joseph
Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com;
<https://www.visena.com;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@visena.com
Subject: Re: Cannot get query to use btree-gin index when ORDER BY
In-Reply-To: <VisenaEmail.63.beee75c83c50054c.14ca0154fe9@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox