[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Problems with FTS
From:       Robert Haas <robertmhaas () gmail ! com>
Date:       2011-11-30 20:58:28
Message-ID: CA+Tgmoaqy-O9OWYnO-Xc0ei_mytB6C2rMAarjwuM+581TGRaLA () mail ! gmail ! com
[Download RAW message or body]

On Mon, Nov 21, 2011 at 12:53 AM, Rauan Maemirov <rauan@maemirov.com> wrote:
> The problem has returned back, and here's the results, as you've said it's
> faster now:
>
> SET enable_seqscan=off;
> EXPLAIN ANALYZE SELECT "v"."id", "v"."title" FROM "video" AS "v"
> WHERE (v.active) AND (v.fts @@
> 'dexter:A|season:A|seri:A|декстер:A|качество:A|сезон:A|серия:A'::tsquery and
> v.id <> 500563 )
> ORDER BY COALESCE(ts_rank_cd( '{0.1, 0.2, 0.7, 1.0}', v.fts,
> 'dexter:A|season:A|seri:A|декстер:A|качество:A|сезон:A|серия:A'::tsquery),
> 1) DESC, v.views DESC
> LIMIT 6
>
> Limit   (cost=219631.83..219631.85 rows=6 width=287) (actual
> time=1850.567..1850.570 rows=6 loops=1)
>    ->   Sort   (cost=219631.83..220059.05 rows=170886 width=287) (actual
> time=1850.565..1850.566 rows=6 loops=1)
>             Sort Key: (COALESCE(ts_rank_cd('{0.1,0.2,0.7,1}'::real[], fts, '( (
> ( ( ( ''dexter'':A | ''season'':A ) | ''seri'':A ) | ''декстер'':A ) |
> ''качество'':A ) | ''сезон'':A ) | ''серия'':A'::tsquery), 1::real)), views
>             Sort Method:   top-N heapsort   Memory: 26kB
>             ->   Bitmap Heap Scan on video v   (cost=41180.92..216568.73
> rows=170886 width=287) (actual time=214.842..1778.830 rows=103087 loops=1)
>                      Recheck Cond: (fts @@ '( ( ( ( ( ''dexter'':A | ''season'':A )
> | ''seri'':A ) | ''декстер'':A ) | ''качество'':A ) | ''сезон'':A ) |
> ''серия'':A'::tsquery)
>                      Filter: (active AND (id <> 500563))
>                      ->   Bitmap Index Scan on idx_video_fts   (cost=0.00..41138.20
> rows=218543 width=0) (actual time=170.206..170.206 rows=171945 loops=1)
>                               Index Cond: (fts @@ '( ( ( ( ( ''dexter'':A |
> ''season'':A ) | ''seri'':A ) | ''декстер'':A ) | ''качество'':A ) |
> ''сезон'':A ) | ''серия'':A'::tsquery)
> Total runtime: 1850.632 ms
>
>
> Should I use this instead?

Can you also provide EXPLAIN ANALYZE output for the query with
enable_seqscan=on?

The row-count estimates look reasonably accurate, so there's some
other problem here.  What do you have random_page_cost, seq_page_cost,
and effective_cache_size set to?  You might try "SET
random_page_cost=2" or even "SET random_page_cost=0.5; SET
seq_page_cost=0.3" and see if those settings help.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[prev in list] [next in list] [prev in thread] [next in thread]