From: Rainer Mager <rainer@vanten.com>
To: pgsql-performance@postgresql.org
Subject: why does this use the wrong index?
Date: Fri, 19 Sep 2008 09:34:32 +0900
Message-ID: <000101c919ef$7c0b8e20$7422aa60$@com> (raw)
I have two identical queries except for the date range. In the first case,
with the wider date range, the correct (I believe) index is used. In the
second case where the date range is smaller a different index is used and a
less efficient plan is chosen. In the second query the problem seems to be
CPU resoures; while it is running 1 core of the CPU is 100% busy.
Note, if I drop the ad_log_date index then this query is always fast, but
some other queries I do require that index.
So, What can I do to encourage Postgres to use the first index even when the
date range is smaller.
# explain analyze SELECT name FROM players AS foo WHERE EXISTS (SELECT 1
FROM ad_log WHERE player = foo.id AND date(start_time) BETWEEN
E'2008-09-14' AND E'2008-09-18' LIMIT 1) ORDER BY name;
QUERY
PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-
Sort (cost=1573.74..1574.31 rows=230 width=13) (actual time=28.421..28.505
rows=306 loops=1)
Sort Key: foo.name
Sort Method: quicksort Memory: 28kB
-> Seq Scan on players foo (cost=0.00..1564.72 rows=230 width=13)
(actual time=0.104..27.876 rows=306 loops=1)
Filter: (subplan)
SubPlan
-> Limit (cost=0.01..3.39 rows=1 width=0) (actual
time=0.058..0.058 rows=1 loops=460)
-> Index Scan using ad_log_player_date on ad_log
(cost=0.01..34571.03 rows=10228 width=0) (actual time=0.056..0.056 rows=1
loops=460)
Index Cond: ((player = $0) AND (date(start_time) >=
'2008-09-14'::date) AND (date(start_time) <= '2008-09-18'::date))
Total runtime: 28.623 ms
(10 rows)
# explain analyze SELECT name FROM players AS foo WHERE EXISTS (SELECT 1
FROM ad_log WHERE player = foo.id AND date(start_time) BETWEEN
E'2008-09-18' AND E'2008-09-18' LIMIT 1) ORDER BY name;
QUERY PLAN
----------------------------------------------------------------------------
-----------------------------------------------------------------
Index Scan using players_name_key on players foo (cost=0.00..8376.84
rows=230 width=13) (actual time=813.695..143452.810 rows=301 loops=1)
Filter: (subplan)
SubPlan
-> Limit (cost=0.01..18.14 rows=1 width=0) (actual
time=311.846..311.846 rows=1 loops=460)
-> Index Scan using ad_log_date on ad_log (cost=0.01..18.14
rows=1 width=0) (actual time=311.844..311.844 rows=1 loops=460)
Index Cond: ((date(start_time) >= '2008-09-18'::date) AND
(date(start_time) <= '2008-09-18'::date))
Filter: (player = $0)
Total runtime: 143453.100 ms
(8 rows)
Thanks,
--Rainer
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-performance@postgresql.org
Cc: rainer@vanten.com
Subject: Re: why does this use the wrong index?
In-Reply-To: <000101c919ef$7c0b8e20$7422aa60$@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