From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dan Harris <fbsd@drivefaster.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Encouraging multi-table join order
Date: Mon, 10 Apr 2006 21:01:37 -0400
Message-ID: <2406.1144717297@sss.pgh.pa.us> (raw)
In-Reply-To: <443AEF9B.3000909@drivefaster.net>
References: <443AAC26.4070802@drivefaster.net>
<1543.1144710756@sss.pgh.pa.us>
<443AEF9B.3000909@drivefaster.net>
Dan Harris <fbsd@drivefaster.net> 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
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: tgl@sss.pgh.pa.us, fbsd@drivefaster.net
Subject: Re: Encouraging multi-table join order
In-Reply-To: <2406.1144717297@sss.pgh.pa.us>
* 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