...

или внеочередные заметки


Previous Entry Share Next Entry
Obsoleted FTS trick
trekking, Himalaya
obartunov
Many years I recommended to use subselects to avoid extra ts_headline() evaluation:
select ts_headline(body,to_tsquery('supernovae & x-ray')), rank from (
  select body, ts_rank(fts,to_tsquery('supernovae & x-ray')) as rank 
  from apod 
  where fts @@ to_tsquery('supernovae & x-ray')
  order by rank desc limit 5
) as foo;


But modern version of Postgres 9.6 is wise enough (commit 9118d03a8cca3d97327c56bf89a72e328e454e63: When appropriate, postpone SELECT output expressions till after ORDER BY) and now one could write much simple sql:
select ts_headline(body,to_tsquery('supernovae & x-ray')), ts_rank(fts,to_tsquery('supernovae & x-ray')) as rank 
from apod
where fts  @@ to_tsquery('supernovae & x-ray') 
order by rank desc limit 5


I wrote simple tsheadline wrapper and learned that ts_headline() in the last sql query was called exactly 5 times.

=================================================================================
Now, it'd be great if postgres handles even simpler version of sql:

select ts_headline(body,q),ts_rank(fts,q) as rank
from apod, to_tsquery('supernovae & x-ray') q
where fts  @@ q 
order by rank desc limit 5;


Unfortunately, this form of query could be very slow, since postgres will use join (nested loop), so, don't use this!
 Limit (actual time=0.457..1.153 rows=5 loops=1)
   ->  Result (actual time=0.456..1.150 rows=5 loops=1)
         ->  Sort (actual time=0.284..0.285 rows=5 loops=1)
               Sort Key: (ts_rank(apod.fts, q.q)) DESC
               Sort Method: top-N heapsort  Memory: 32kB
               ->  Nested Loop (actual time=0.085..0.267 rows=36 loops=1)
                     ->  Function Scan on to_tsquery q (actual time=0.022..0.022 rows=1 loops=1)
                     ->  Bitmap Heap Scan on apod (actual time=0.045..0.074 rows=36 loops=1)
                           Recheck Cond: (fts @@ q.q)
                           Heap Blocks: exact=34
                           ->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.037..0.037 rows=36 loops=1)
                                 Index Cond: (fts @@ q.q)
 Planning time: 0.079 ms
 Execution time: 1.182 ms
(14 rows)
Tags: , ,

?

Log in

No account? Create an account