From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bricklen Anderson <BAnderson@PresiNET.com>
Cc: David Gagnon <dgagnon@siunik.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Please help with this explain analyse...
Date: Mon, 28 Nov 2005 19:00:30 -0500
Message-ID: <17298.1133222430@sss.pgh.pa.us> (raw)
In-Reply-To: <438B96C6.4090902@PresiNET.com>
References: <438B958B.3010602@siunik.com>
<438B96C6.4090902@PresiNET.com>
Bricklen Anderson <BAnderson@PresiNET.com> writes:
> Your loops are what is causing the time spent.
> eg. "actual time=0.127..17.379 rows=1154 loops=8335)" ==
> 8335*(17.379-0.127)/1000=>143 secs (if my math is correct).
As for where the problem is, I think it's the horrid misestimate of the
number of matching rows in cs_pk:
>> " -> Index Scan using cs_pk on cs (cost=0.00..12.83
>> rows=2 width=144) (actual time=0.087..444.999 rows=13587 loops=1)"
>> " Index Cond: (('M'::text = (cs.csypnum)::text)
>> AND ((cs.csglnum)::text = ("outer".glnum)::text))"
Has that table been ANALYZEd recently? How about the gl table?
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, BAnderson@PresiNET.com, dgagnon@siunik.com
Subject: Re: Please help with this explain analyse...
In-Reply-To: <17298.1133222430@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