From: Robert Creager <Robert_Creager@LogicalChaos.org>
To: PGPerformance <pgsql-performance@postgresql.org>
Subject: Index isn't used during a join.
Date: Mon, 9 Jan 2006 21:23:38 -0700
Message-ID: <20060109212338.6420af12@thunder.logicalchaos.org> (raw)
Hey folks,
I'm working with a query to get more info out with a join. The base query works great speed wise because of index usage. When the join is tossed in, the index is no longer used, so the query performance tanks.
Can anyone advise on how to get the index usage back?
weather=# select version();
version
-----------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1 (4.0.1-5mdk for Mandriva Linux release 2006.0)
(1 row)
The base query is:
weather=# EXPLAIN ANALYZE
weather-# SELECT min_reading, max_reading, avg_reading, -- doy,
weather-# unmunge_time( time_group ) AS time
weather-# FROM minute."windspeed"
weather-# --JOIN readings_doy ON EXTRACT( doy FROM unmunge_time( time_group ) ) = doy
weather-# WHERE unmunge_time( time_group ) > ( now() - '24 hour'::interval )
weather-# ORDER BY time_group;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=10995.29..11155.58 rows=64117 width=28) (actual time=4.509..4.574 rows=285 loops=1)
Sort Key: time_group
-> Bitmap Heap Scan on windspeed (cost=402.42..5876.05 rows=64117 width=28) (actual time=0.784..3.639 rows=285 loops=1)
Recheck Cond: (unmunge_time(time_group) > (now() - '24:00:00'::interval))
-> Bitmap Index Scan on minute_windspeed_index (cost=0.00..402.42 rows=64117 width=0) (actual time=0.675..0.675 rows=285 loops=1)
Index Cond: (unmunge_time(time_group) > (now() - '24:00:00'::interval))
Total runtime: 4.880 ms
(7 rows)
When I add in the join, the query tosses out the nice quick index in favor of sequence scans:
weather=# EXPLAIN ANALYZE
weather-# SELECT min_reading, max_reading, avg_reading, -- doy,
weather-# unmunge_time( time_group ) AS time
weather-# FROM minute."windspeed"
weather-# JOIN readings_doy ON EXTRACT( doy FROM unmunge_time( time_group ) ) = doy
weather-# WHERE unmunge_time( time_group ) > ( now() - '24 hour'::interval )
weather-# ORDER BY time_group;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=98239590.88..99052623.66 rows=325213113 width=28) (actual time=60136.484..61079.845 rows=1030656 loops=1)
Sort Key: windspeed.time_group
-> Merge Join (cost=265774.21..8396903.54 rows=325213113 width=28) (actual time=34318.334..47113.277 rows=1030656 loops=1)
Merge Cond: ("outer"."?column5?" = "inner"."?column2?")
-> Sort (cost=12997.68..13157.98 rows=64120 width=28) (actual time=2286.155..2286.450 rows=284 loops=1)
Sort Key: date_part('doy'::text, unmunge_time(windspeed.time_group))
-> Seq Scan on windspeed (cost=0.00..7878.18 rows=64120 width=28) (actual time=2279.275..2285.271 rows=284 loops=1)
Filter: (unmunge_time(time_group) > (now() - '24:00:00'::interval))
-> Sort (cost=252776.54..255312.51 rows=1014389 width=8) (actual time=32001.370..33473.407 rows=1051395 loops=1)
Sort Key: date_part('doy'::text, readings."when")
-> Seq Scan on readings (cost=0.00..142650.89 rows=1014389 width=8) (actual time=0.053..13759.015 rows=1014448 loops=1)
Total runtime: 61720.935 ms
(12 rows)
weather=# \d minute.windspeed
Table "minute.windspeed"
Column | Type | Modifiers
-------------+------------------+-----------
time_group | integer | not null
min_reading | double precision | not null
max_reading | double precision | not null
avg_reading | double precision | not null
Indexes:
"windspeed_pkey" PRIMARY KEY, btree (time_group)
"minute_windspeed_index" btree (unmunge_time(time_group))
CREATE OR REPLACE FUNCTION unmunge_time( integer )
RETURNS timestamp AS '
DECLARE
input ALIAS FOR $1;
BEGIN
RETURN (''epoch''::timestamptz + input * ''1sec''::interval)::timestamp;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;
weather=# \d readings
Table "public.readings"
Column | Type | Modifiers
----------------------+-----------------------------+-------------------------------------------------------------
when | timestamp without time zone | not null default (timeofday())::timestamp without time zone
hour_group | integer |
minute_group | integer |
day_group | integer |
week_group | integer |
month_group | integer |
year_group | integer |
year_group_updated | boolean | default false
month_group_updated | boolean | default false
week_group_updated | boolean | default false
day_group_updated | boolean | default false
hour_group_updated | boolean | default false
minute_group_updated | boolean | default false
Indexes:
"readings_pkey" PRIMARY KEY, btree ("when")
"day_group_updated_index" btree (day_group_updated, day_group)
"hour_group_updated_index" btree (hour_group_updated, hour_group)
"month_group_updated_index" btree (month_group_updated, month_group)
"readings_doy_index" btree (date_part('doy'::text, "when"))
"week_group_updated_index" btree (week_group_updated, week_group)
"year_group_updated_index" btree (year_group_updated, year_group)
Triggers:
munge_time BEFORE INSERT OR UPDATE ON readings FOR EACH ROW EXECUTE PROCEDURE munge_time()
readings_doy is a view that adds date_part('doy'::text, readings."when") AS doy to the readings table.
Thanks,
Rob
--
21:15:51 up 2 days, 13:42, 9 users, load average: 3.14, 2.63, 2.62
Linux 2.6.12-12-2 #4 SMP Tue Jan 3 19:56:19 MST 2006
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: Robert_Creager@LogicalChaos.org
Subject: Re: Index isn't used during a join.
In-Reply-To: <20060109212338.6420af12@thunder.logicalchaos.org>
* 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