0

If customer put filter in the as of now query result is very slow. Do i need to create index on all the filter columns in a single index or individual columns. the big issue is sorting.

Below is the query

select it.conversation_id, it.conversation_source, it.full_name,
    it.last_message_timestamp, it.conversation_read,
    it.id as ticket_id, it.owner_id, it.status, it.priority,
    it.tags, it.last_msg_content, it.last_msg_attachment,
    it.last_msg_actor, it.last_msg_delivered, it.last_msg_read,
    it.last_message_timestamp as message_created_at, it.subject,
    it.closed_at, it.last_message_received_at
from inbox_ticket it
where
    it.merchant_id = 115 and it.status <> 'assigned_to_bot' 
    and it.owner_id in (3486070,60,-1)  
    and it.last_message_timestamp >= '2023-07-01 00:00:00+05:30' 
    and it.last_message_timestamp <= '2023-07-31 00:00:00+05:30'
    and it.last_message_timestamp < '2023-07-31 18:40:00+05:30' 
order by it.last_message_timestamp desc, it.id desc 
limit 25

Sorting includes two columns. Do i have to create index separately like index on (last_message_timestamp desc, id desc). Or All the filters + (last_message_timestamp desc, id desc)

I did not tried anything, since the data is huge. Currently it is 6.4 million

Currently if i do explain i get below result.

Limit (cost=4.18..4.19 rows=1 width=218) -> Sort (cost=4.18..4.19 rows=1 width=218) Sort Key: last_message_timestamp DESC, id DESC -> Index Scan using inbox_ticket_merchant_id_67656847 on inbox_ticket it (cost=0.14..4.17 rows=1 width=218) Index Cond: (merchant_id = 115) Filter: (((status)::text <> 'assigned_to_bot'::text) AND (last_message_timestamp >= '2023-07-01 00:00:00+05:30'::timestamp with time zone) AND (last_message_timestamp <= '2023-07-31 00:00:00+05:30'::timestamp with time zone) AND (last_message_timestamp < '2023-07-31 18:40:00+05:30'::timestamp with time zone) AND (owner_id = ANY ('{3486070,60,-1}'::integer[])))

pramod N K
  • 3
  • 2
  • Could you please share the results from explain(analyze, verbose, buffers, settings) for this statement? And the ddl for all tables and indexes involved? All in plain text, as an update to your original question. – Frank Heikens Jul 31 '23 at 15:05
  • Added @FrankHeikens – pramod N K Aug 29 '23 at 10:15
  • This is not the (complete) result from `explain(analyze, verbose, buffers, settings) `for your statement, timing is one of the things missing. The DDL is also missing. – Frank Heikens Aug 29 '23 at 13:40

1 Answers1

0

LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ partition_by_clause ] order_by_clause )

Frederico da paulinas Paulinas
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 06 '23 at 08:06
  • Hi @Frederico can you pls share the actual index for the above query – pramod N K Aug 29 '23 at 10:22