> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Dan Harris
>
> After some digging, I've found that the planner is choosing
> to apply a necessary seq scan to the table. Unfortunately,
> it's scanning the whole table, when it seems that it could
> have joined it to a smaller table first and reduce the
> amount of rows it would have to scan dramatically ( 70
> million to about 5,000 ).
>
Joining will reduce the amount of rows to scan for the filter, but
performing the join is non-trivial. If postgres is going to join two tables
together without applying any filter first then it will have to do a seqscan
of one of the tables, and if it chooses the table with 5000 rows, then it
will have to do 5000 index scans on a table with 70 million records. I
don't know which way would be faster.
I wonder if you could find a way to use an index to do the text filter.
Maybe tsearch2? I haven't used anything like that myself, maybe someone
else has more input.