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
Date: Mon, 3 Mar 2003 14:09:35 -0800 (PST)
Message-ID: <20030303140649.H42776-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <D8B0CD90-4DBB-11D7-90E0-000A27935D5A@nachbaur.com>
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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, mike@nachbaur.com
Subject: Re: Forcing query to use an index
In-Reply-To: <20030303140649.H42776-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox