Reputation: 1144
select t.createdDate, t.createdDateTicks from ic_v10_mammoet t where t.type='asset' and t._sync.rev is not null ORDER BY t.createdDateTicks ASC LIMIT 10 OFFSET 0
The above query takes 6 seconds to return result and when I remove ORDER BY clause it take only 18 MS
select t.createdDate, t.createdDateTicks from ic_v10_mammoet t where t.type='asset' and t._sync.rev is not null LIMIT 10 OFFSET 0
I have an index on createdDateTicks and it is integer field.
I tried the workaround mentioned at workaround as last comment but that do not work.
Can someone please advise?
Index is:CREATE INDEX asset_createdDateTicks ON ic_v10_mammoet (createdDateTicks) WHERE type = 'asset'
Plan with ORDER BY and Plan without ORDER BY
Upvotes: 0
Views: 1379
Reputation: 2445
Here is the workaround.
CREATE INDEX idx_neg_date ON docs( -createDateTicks ) WHERE type = 'asset';
SELECT t.createdDate, t.createdDateTicks
FROM docs AS t
WHERE t.type='asset' AND -t.createdDateTicks IS NOT NULL
ORDER BY -t.createdDateTicks ASC LIMIT 10 OFFSET 0;
Upvotes: 1