Re: Using FTI-Search (likely a more general runtime-puzzle)

Hello!
 
You seem to have hit a bullseye here :) p.article_id is of course unique
and your suggestion gives us everything we could ask for - in just under
60ms - that's not only wonderful but lightning fast! As we are not
planning on switching the database at all (we are just finishing
migration from Oracle to PostgreSQL), we are more than happy with any
extension that does the job so much, much faster.
 
Thank you very much!
 
Regards,
 
   Markus Wollny

    -----Ursprüngliche Nachricht----- 
    Von: Stephan Szabo 
    Gesendet: Mi 24.07.2002 19:07 
    An: Markus Wollny 
    Cc: pgsql-general@postgresql.org 
    Betreff: Re: [GENERAL] Using FTI-Search (likely a more general
runtime-puzzle)
    
    


    > This is way too long. I don't know why it doesn't use the
indexes I gave
    > to it... If someone has got some idea as to what can be done
to optimize
    > query-planner decisions, I'd be happy to comply. But anyway, I
don't
    > worry about this too much and just force it to use them, once
I know its
    > decision for seqential scan is wrong:
    
    Have you vaccum analyzed the fti table?
    
    > Case 3b:
    > Retrieving additional article-info:
    >
    > SET ENABLE_SEQSCAN=OFF;
    > EXPLAIN ANALYZE
    > SELECT article_id, site_id, article_type, topstory, headline,
published
    > from article where article_id in (
    > select distinct (p.article_id)
    > from article p, article_fti f1, article_fti f2, article_fti f3
    > where f1.string ='grand' and f2.string ='theft' and f3.string
='auto'
    > and p.oid=f1.id and p.oid=f2.id and p.oid=f3.id);
    > SET ENABLE_SEQSCAN=ON;
    
    I was going to suggest a subselect in from rather than the IN,
    but perhaps you could just use distinct on and get the data from
p.
    Admittedly this uses a postgresql extension, and assumes that
p.article_id
    is unique, maybe like:
    
    select distinct on (p.article_id) p.* from
     article p, article_fti f1, article_fti f2, article_fti f3
     where f1.string ='grand' and f2.string ='theft' and f3.string
='auto'
     and p.oid=f1.id and p.oid=f2.id and p.oid=f3.id;