From: "Dave Dutcher" <dave@tridecap.com>
To: "'Dan Harris'" <fbsd@drivefaster.net>,
	"'PostgreSQL Performance'" <pgsql-performance@postgresql.org>
Subject: Re: Planner doing seqscan before indexed join
Date: Thu, 29 Mar 2007 19:15:23 -0500
Message-ID: <019201c77260$82f91fa0$2e00a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AcdxqWmxekhlFV31QDmwP0bfS/UQUAAs7RGA
In-Reply-To: <460B22E1.7070904@drivefaster.net>
Importance: Normal

> From: pgsql-performance-owner@postgresql.org=20
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20
> Dan Harris
>=20
> After some digging, I've found that the planner is choosing=20
> to apply a necessary seq scan to the table.  Unfortunately,
> it's scanning the whole table, when it seems that it could=20
> have joined it to a smaller table first and reduce the
> amount of rows it would have to scan dramatically ( 70=20
> million to about 5,000 ).
>=20

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.=20

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.