X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 43BAA475AD7
	for <pgsql-sql@postgresql.org>; Mon,  3 Mar 2003 17:38:27 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 19520-08 for <pgsql-sql@postgresql.org>;
	Mon,  3 Mar 2003 17:38:21 -0500 (EST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id E69CA476124
	for <pgsql-sql@postgresql.org>; Mon,  3 Mar 2003 17:09:38 -0500 (EST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 8A317D613; Mon,  3 Mar 2003 14:09:35 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 7FEB85C03; Mon,  3 Mar 2003 14:09:35 -0800 (PST)
Date: Mon, 3 Mar 2003 14:09:35 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Michael Nachbaur <mike@nachbaur.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Forcing query to use an index
In-Reply-To: <D8B0CD90-4DBB-11D7-90E0-000A27935D5A@nachbaur.com>
Message-ID: <20030303140649.H42776-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200303/94
X-Sequence-Number: 12134


On Mon, 3 Mar 2003, Michael Nachbaur wrote:

> Hello everyone,
>
> I have a search query that does a whole bunch of LEFT OUTER JOINs
> between multiple tables, since this is a generic search and some
> records may not exist for certain customers (e.g. searching for the
> text "kate" should bring up people whose customer name, street address
> or email addresses match that word).  This is for an ISP's customer
> management database.
>
> Unfortunately one stage in the query keeps using a sequence scan rather
> than the index.  Here is the "EXPLAIN ANALYZE" results for the 115 line
> SQL query.
>
> Sort  (cost=6666.08..6666.08 rows=268 width=265) (actual
> time=949.00..949.00 rows=1 loops=1)
>    ->  Aggregate  (cost=6487.84..6655.27 rows=268 width=265) (actual
> time=948.86..948.86 rows=1 loops=1)
>          ->  Group  (cost=6487.84..6648.58 rows=2679 width=265) (actual
> time=948.70..948.70 rows=1 loops=1)
>                ->  Sort  (cost=6487.84..6487.84 rows=2679 width=265)
> (actual time=948.66..948.66 rows=1 loops=1)
>                      ->  Merge Join  (cost=6106.42..6335.30 rows=2679
> width=265) (actual time=859.77..948.06 rows=1 loops=1)
>                            ->  Merge Join  (cost=6101.24..6319.77
> rows=2679 width=247) (actual time=554.11..674.17 rows=2679 loops=1)
>                                  ->  Index Scan using customer_id_key on
> customer c  (cost=0.00..129.63 rows=2679 width=156) (actual
> time=0.40..43.43 rows=2679 loops=1)
>                                  ->  Sort  (cost=6101.24..6101.24
> rows=8117 width=91) (actual time=553.64..559.58 rows=8117 loops=1)
>                                        ->  Seq Scan on
> customer_month_summary cms  (cost=0.00..5574.17 rows=8117 width=91)
> (actual time=258.03..477.11 rows=8117 loops=1)
>                            ->  Sort  (cost=5.18..5.18 rows=77 width=18)
> (actual time=0.70..0.80 rows=77 loops=1)
>                                  ->  Seq Scan on emailaddress ea
> (cost=0.00..2.77 rows=77 width=18) (actual time=0.08..0.35 rows=77
> loops=1)
> Total runtime: 951.70 msec
>
> The table in question is "customer_month_summary"; it has 8117 rows.

If you're hitting all the rows in the table, there's only disadvantage
to using an indexscan (right now, given the way data is stored).  If you
were returning some fraction of the rows postgresql should hopefully
switch to a different plan (depending on the estimated costs).