I have a table
messages in a Postgres 9.4 database. Messages belong to
feed_id, and have
posted_at. Also, messages can have a parent message (in case of replies).
Table "public.messages" Column | Type | Modifiers ------------------------------+-----------------------------+----------- message_id | character varying(255) | not null feed_id | integer | parent_id | character varying(255) | posted_at | timestamp without time zone | share_count | integer | Indexes: "messages_pkey" PRIMARY KEY, btree (message_id) "index_messages_on_feed_id_posted_at" btree (feed_id, posted_at DESC NULLS LAST)
I want to return all messages ordered by
share_count, but for each
parent_id, I only want to return one message. ie, if multiple messages have the same
parent_id, then only the latest one (
posted_at) is returned. The
parent_id can be null, messages with null
parent_id should all return.
The query I used is:
WITH filtered_messages AS (SELECT * FROM messages WHERE feed_id IN (7) AND (posted_at >= '2015-01-01 04:00:00.000000') AND (posted_at < '2015-04-28 04:00:00.000000')) SELECT * FROM (SELECT DISTINCT ON(COALESCE(parent_id, message_id)) parent_id, message_id, posted_at, share_count FROM filtered_messages ORDER BY COALESCE(parent_id, message_id), posted_at DESC NULLS LAST ) messages ORDER BY share_count DESC NULLS LAST, posted_at DESC NULLS LAST;
Here's an SQL fiddle with schema, exact query, and expected result.
The performance of the query is slow once the
messages table gets big. I tried add multiple sorting indexes, but it does not seem to use the index.
How can I create a correct index?
ORDER BYin the subquery is totally useless. Furthermore, the linked plan cannot be a result of the posted query - there is no mention of
metadata, for example.
posted_atand you did not mention
metadataat all, which seems to be a JSON type? Please repair your question to make it consistent. You select > 500k rows in the CTE ... How many rows are in the table? What percentage of rows do you typically select in the CTE? What percentage of rows has
parent_id IS NULL? Consider the info in the [postgresql-performance] tag for performance questions.
parent_id? (min / avg / max)
metadata. Currently the messages table has 10 mil data, but increasing fast. I am think to separate into partition tables for each feed_id. Since I am only fetching per feed id. the percentage of parent_id null vs not null is about 60% / 40%. a typical fetch is around 1-2% of the table. ( around 100K messages) The performance for 100K is around 1s, but once gets to 500K+ it uses bitmap index and normally takes 10s.