X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 917159DC880 for ; Tue, 10 Jan 2006 00:30:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01658-04 for ; Tue, 10 Jan 2006 00:30:31 -0400 (AST) X-Greylist: delayed 00:06:40.480594 by SQLgrey- Received: from rambo.iniquinet.com (rambo.iniquinet.com [69.39.89.10]) by postgresql.org (Postfix) with ESMTP id D646E9DC87D for ; Tue, 10 Jan 2006 00:30:30 -0400 (AST) Received: (qmail 30906 invoked by uid 1010); 9 Jan 2006 23:23:49 -0500 Received: from Robert_Creager@LogicalChaos.org by rambo.iniquinet.com by uid 1002 with qmail-scanner-1.20 (spamassassin: 3.1.0. Clear:RC:0(63.147.78.66):SA:0(?/?):. Processed in 2.337398 secs); 10 Jan 2006 04:23:49 -0000 Received: from unknown (HELO thunder.logicalchaos.org) (perl?test@logicalchaos.org@63.147.78.66) by rambo.iniquinet.com with AES256-SHA encrypted SMTP; 9 Jan 2006 23:23:46 -0500 Received: from logicalchaos.org (localhost [127.0.0.1]) by thunder.logicalchaos.org (Postfix) with ESMTP id 2231396F9A for ; Mon, 9 Jan 2006 21:23:45 -0700 (MST) Date: Mon, 9 Jan 2006 21:23:38 -0700 From: Robert Creager To: PGPerformance Subject: Index isn't used during a join. Message-ID: <20060109212338.6420af12@thunder.logicalchaos.org> Organization: Starlight Vision, LLC. X-Mailer: Sylpheed-Claws 1.0.4 (GTK+ 1.2.10; i586-mandriva-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; boundary=Signature_Mon__9_Jan_2006_21_23_38_-0700_gN17GIHnFOTBJ1wn; protocol="application/pgp-signature"; micalg=pgp-sha1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200601/80 X-Sequence-Number: 16558 --Signature_Mon__9_Jan_2006_21_23_38_-0700_gN17GIHnFOTBJ1wn Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable 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=3D# select version(); version = =20 ---------------------------------------------------------------------------= -------------------------------------------- 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=3D# 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= ) ) =3D doy weather-# WHERE unmunge_time( time_group ) > ( now() - '24 hour'::interval ) weather-# ORDER BY time_group; QUERY PLAN= =20 ---------------------------------------------------------------------------= ------------------------------------------------------------------ Sort (cost=3D10995.29..11155.58 rows=3D64117 width=3D28) (actual time=3D4= .509..4.574 rows=3D285 loops=3D1) Sort Key: time_group -> Bitmap Heap Scan on windspeed (cost=3D402.42..5876.05 rows=3D64117 = width=3D28) (actual time=3D0.784..3.639 rows=3D285 loops=3D1) Recheck Cond: (unmunge_time(time_group) > (now() - '24:00:00'::int= erval)) -> Bitmap Index Scan on minute_windspeed_index (cost=3D0.00..402= .42 rows=3D64117 width=3D0) (actual time=3D0.675..0.675 rows=3D285 loops=3D= 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=3D# 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 )= ) =3D doy weather-# WHERE unmunge_time( time_group ) > ( now() - '24 hour'::interval ) weather-# ORDER BY time_group; QUERY PLAN = =20 ---------------------------------------------------------------------------= -------------------------------------------------------------- Sort (cost=3D98239590.88..99052623.66 rows=3D325213113 width=3D28) (actua= l time=3D60136.484..61079.845 rows=3D1030656 loops=3D1) Sort Key: windspeed.time_group -> Merge Join (cost=3D265774.21..8396903.54 rows=3D325213113 width=3D2= 8) (actual time=3D34318.334..47113.277 rows=3D1030656 loops=3D1) Merge Cond: ("outer"."?column5?" =3D "inner"."?column2?") -> Sort (cost=3D12997.68..13157.98 rows=3D64120 width=3D28) (act= ual time=3D2286.155..2286.450 rows=3D284 loops=3D1) Sort Key: date_part('doy'::text, unmunge_time(windspeed.time= _group)) -> Seq Scan on windspeed (cost=3D0.00..7878.18 rows=3D6412= 0 width=3D28) (actual time=3D2279.275..2285.271 rows=3D284 loops=3D1) Filter: (unmunge_time(time_group) > (now() - '24:00:00= '::interval)) -> Sort (cost=3D252776.54..255312.51 rows=3D1014389 width=3D8) (= actual time=3D32001.370..33473.407 rows=3D1051395 loops=3D1) Sort Key: date_part('doy'::text, readings."when") -> Seq Scan on readings (cost=3D0.00..142650.89 rows=3D101= 4389 width=3D8) (actual time=3D0.053..13759.015 rows=3D1014448 loops=3D1) Total runtime: 61720.935 ms (12 rows) weather=3D# \d minute.windspeed Table "minute.windspeed" Column | Type | Modifiers=20 -------------+------------------+----------- 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=3D# \d readings Table "public.readings" Column | Type | = Modifiers =20 ----------------------+-----------------------------+----------------------= --------------------------------------- when | timestamp without time zone | not null default (tim= eofday())::timestamp without time zone hour_group | integer |=20 minute_group | integer |=20 day_group | integer |=20 week_group | integer |=20 month_group | integer |=20 year_group | integer |=20 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 PRO= CEDURE munge_time() readings_doy is a view that adds date_part('doy'::text, readings."when") AS= doy to the readings table. Thanks, Rob --=20 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 --Signature_Mon__9_Jan_2006_21_23_38_-0700_gN17GIHnFOTBJ1wn Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (GNU/Linux) iEYEARECAAYFAkPDNtAACgkQLQ/DKuwDYzno5QCgisAGadWtWJ6KGQlT6nc1SuEE tVsAn0144x8/1a4rYxqcRslobs3pnAjP =6XVQ -----END PGP SIGNATURE----- --Signature_Mon__9_Jan_2006_21_23_38_-0700_gN17GIHnFOTBJ1wn--