X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0CEEC475AD7
	for <pgsql-sql@postgresql.org>; Mon,  3 Mar 2003 18:32:21 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 33444-01 for <pgsql-sql@postgresql.org>;
	Mon,  3 Mar 2003 18:32:19 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 91E32475A1F
	for <pgsql-sql@postgresql.org>; Mon,  3 Mar 2003 18:32:12 -0500 (EST)
Received: from [66.219.92.2] (HELO chocolate-mousse)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2878212; Mon, 03 Mar 2003 15:31:59 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Michael Nachbaur <mike@nachbaur.com>, pgsql-sql@postgresql.org
Subject: Re: Forcing query to use an index
Date: Mon, 3 Mar 2003 15:32:47 -0800
X-Mailer: KMail [version 1.4]
References: <9959CFC2-4DC7-11D7-90E0-000A27935D5A@nachbaur.com>
In-Reply-To: <9959CFC2-4DC7-11D7-90E0-000A27935D5A@nachbaur.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200303031532.47899.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200303/101
X-Sequence-Number: 12141


Micheal,

Issues with your query:

  LEFT OUTER JOIN Customer_Month_Summary AS CMS
    ON ( C.ID =3D CMS.CustomerID
     AND CMS.MonthStart =3D DATE '2003-02-01'
       )

Check out the thread: Re: [SQL] OUTER JOIN with filter=20
in today's list; this relates to your problem.  Then try your query as:

  LEFT OUTER JOIN (SELECT * FROM Customer_Month_Summary=20
    WHRE CMS.MonthStart =3D DATE '2003-02-01'
       ) CMS ON C.ID =3D CMS.CustomerID

This may make better use of your index, because the planner will have a mor=
e=20
accurate estimate of the number of rows returned from the outer join.

AND:

   AND ( C.Accountnum                            ~* 'kate'
      OR C.Firstname                             ~* 'kate'
      OR C.Lastname                              ~* 'kate'
      OR C.Organization                          ~* 'kate'
      OR C.Address                               ~* 'kate'
      OR C.Postal                                ~* 'kate'
      OR C.City                                  ~* 'kate'
      OR EA.Name || '@' || JoinDomain(EA.Domain) ~* 'kate'

This set of expressions has "seq scan" written all over it.   I hihgly sugg=
est=20
that you try to find a way to turn these into anchored text searches, perha=
ps=20
using functional indexes on lower(column).

Finally:

      OR CMS.Package                             ~* 'kate'

Per the above, this is why Postgres cannot use an index on your table; that=
 is=20
an unanchored text search which can *only* be indexed using FTS.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco