> 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;