From: Ragnar <gnari@hive.is>
To: Doron Baranes <doron.baranes@dbnet.co.il>
Cc: pgsql-performance@postgresql.org
Subject: Re:
Date: Mon, 10 Apr 2006 11:59:29 +0000
Message-ID: <1144670369.32289.165.camel@localhost.localdomain> (raw)
In-Reply-To: <0BA77301DFF4B24C9C5DAA74138BF95001E202@exchange-1.dbnet.co.il>
References: <0BA77301DFF4B24C9C5DAA74138BF95001E202@exchange-1.dbnet.co.il>
On mán, 2006-04-10 at 10:30 +0200, Doron Baranes wrote:
> I Attached here a file with details about the tables, the queries and
> the
> Explain analyze plans.
> Hope this can be helpful to analyze my problem
first query:
> explain analyze SELECT date_trunc('hour'::text,
> i.entry_time) AS datetime,
> COUNT(fr.grp_fate_id) ,
> SUM(i.size)
> FROM log.msg_info as i,log.msg_fate as f,
> log.msg_fate_recipients as fr
> WHERE i.origin = 1
> AND i.msgid=f.msgid
> AND i.entry_time > '2006-01-25'
> AND f.grp_fate_id=fr.grp_fate_id
> GROUP BY datetime
> order by datetime;
if i.origin has high selectivity (if very
few rows in msg_info have origin=1 in this
case), an index on msg_info(orgin) can help.
unfortunately, as you are using 7.4 and this
is a smallint column, you would have to change
the query slightly to make use of that:
WHERE i.origin = 1::smallint
if more than a few % or the rows have this value,
then this will not help
the index on msg_info(entry_time) is unlikely
to be used, because a simple '>' comparison
has little selectivity. try to add an upper limit
to the query to make it easier for the planner
so see that few rows would be returned (if that is
the case)
for example:
AND i.entry_time BETWEEN '2006-01-25'
AND '2006-05-01'
this might also improve the estimated number
of groups on datetime (notice: estimated rows=1485233,
real=623), although I am not sure if that will help you
I do now know how good the planner is with dealing
with the date_trunc('hour'::text, i.entry_time),
so possibly you could get some improvement with
an indexed entry_hour column populated with trigger
or by your application, and change your query to:
explain analyze SELECT i.entry_hour,
COUNT(fr.grp_fate_id) ,
SUM(i.size)
FROM log.msg_info as i,log.msg_fate as f, log.msg_fate_recipients as fr
WHERE i.origin = 1
AND i.msgid=f.msgid
AND i.entry_hour BETWEEN '2006-01-25:00:00'
AND '2006-05-01:00:00'
AND f.grp_fate_id=fr.grp_fate_id
GROUP BY entry_hour
order by entry_hour;
(adjust the upper limit to your reality)
do these suggestions help at all?
gnari
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: gnari@hive.is, doron.baranes@dbnet.co.il
Subject: Re:
In-Reply-To: <1144670369.32289.165.camel@localhost.localdomain>
* 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