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 2182911F6B69 for ; Mon, 10 Apr 2006 23:05:14 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.126 X-Spam-Level: X-Spam-Status: No, score=0.126 required=5 tests=[AWL=0.126] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id WFOBD6h4T0op for ; Mon, 10 Apr 2006 23:03:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 6F2F211F662A for ; Mon, 10 Apr 2006 22:01:37 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k3B11bR4002407; Mon, 10 Apr 2006 21:01:37 -0400 (EDT) To: Dan Harris cc: PostgreSQL Performance Subject: Re: Encouraging multi-table join order In-reply-to: <443AEF9B.3000909@drivefaster.net> References: <443AAC26.4070802@drivefaster.net> <1543.1144710756@sss.pgh.pa.us> <443AEF9B.3000909@drivefaster.net> Comments: In-reply-to Dan Harris message dated "Mon, 10 Apr 2006 17:51:55 -0600" Date: Mon, 10 Apr 2006 21:01:37 -0400 Message-ID: <2406.1144717297@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200604/222 X-Sequence-Number: 18306 Dan Harris writes: > Yes, eventactivity.incidentid is indexed. The datatype is varchar(40). > Although, by checking this, I noticed that k_h.incidentid was > varchar(100). Perhaps the difference in length between the keys caused > the planner to not use the fastest method? No, the planner wouldn't care about that. > Here's the EXPLAIN analyze with enable_hashjoin = off and > enable_mergejoin = off : OK, so it does consider the "right" plan, but it's estimating it'll take longer than the other one. One thing that's very strange is that the estimated number of rows out has changed ... did you re-ANALYZE since the previous message? > -> Index Scan using eventactivity1 on > eventactivity (cost=0.00..5774.81 rows=20 width=52) (actual > time=29.768..51.334 rows=3 loops=1162) > Index Cond: (("outer".incidentid)::text = > (eventactivity.incidentid)::text) > Filter: ((((' '::text || (recordtext)::text) > || ' '::text) ~~ '%HAL%'::text) AND (entrydate >= '2006-01-01 > 00:00:00'::timestamp without time zone) AND (entrydate < '2006-04-08 > 00:00:00'::timestamp without time zone)) So it's estimating 5775 cost units per probe into eventactivity, which is pretty high --- it must think that a lot of rows will be retrieved by the index (way more than the 20 or so it thinks will get past the filter condition). What does the pg_stats entry for eventactivity.incidentid contain? It might be worth increasing the statistics target for that column to try to get a better estimate. regards, tom lane