I'm trying to do a query on a table, it gives me the correct result it is slowl when there is an order by .

explain  analyze  select *
from "articles"
cross join plainto_tsquery(COALESCE('russian', get_current_ts_config()), 'search')
AS "tsquery" where searchable @@ "tsquery" order by "date" desc limit 10 offset 0;

Without order by I get a result in 50ms but with the order by it takes 5 seconds.

I tried to fix it by adding this index

CREATE INDEX articles_desc_index ON articles (date DESC NULLS LAST)

But it does not work

    Limit  (cost=2954.42..2954.44 rows=10 width=1520) (actual time=2999.486..2999.504 rows=10 loops=1)
  ->  Sort  (cost=2954.42..2956.15 rows=692 width=1520) (actual time=2999.484..2999.497 rows=10 loops=1)
        Sort Key: articles.published_at DESC
        Sort Method: top-N heapsort  Memory: 44kB
        ->  Nested Loop  (cost=457.36..2939.47 rows=692 width=1520) (actual time=28.453..2981.421 rows=5577 loops=1)
              ->  Function Scan on tsquery  (cost=0.00..0.01 rows=1 width=32) (actual time=0.004..0.006 rows=1 loops=1)
              ->  Bitmap Heap Scan on articles  (cost=457.36..2932.54 rows=692 width=1488) (actual time=28.441..2970.125 rows=5577 loops=1)
                    Recheck Cond: (searchable @@ tsquery.tsquery)
                    Heap Blocks: exact=5197
                    ->  Bitmap Index Scan on articles_searchable_index  (cost=0.00..457.19 rows=692 width=0) (actual time=27.755..27.755 rows=5587 loops=1)
                          Index Cond: (searchable @@ tsquery.tsquery)
Planning time: 0.180 ms
Execution time: 2999.576 ms
  • Thanks, apparently I hurried to seek help, an attempt to create an index without NULLS LAST led to a good result But it is possible for someone to read it, reading the documentation – symfony_fan Sep 26 '18 at 8:35
  • 1
    Please edit your question and add the execution plan generated using explain (analyze, buffers) for the slow query as formatted text please – a_horse_with_no_name Sep 26 '18 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.