From: Linux Guru <linux.binary@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update with Subquery Performance
Date: Tue, 12 Feb 2008 13:32:29 +0500
Message-ID: <3caa866c0802120032r7b7eb672s98d38f1351622424@mail.gmail.com> (raw)
In-Reply-To: <2982.1202749162@sss.pgh.pa.us>
References: <3caa866c0802110306rcb5e72dyd264ff330c61f8ae@mail.gmail.com>
<2982.1202749162@sss.pgh.pa.us>
Analyzing did not help, here is the out of EXPLAIN ANALYZE of update query
"Seq Scan on dummy (cost=0.00..56739774.24 rows=23441 width=275) (actual
time=18.927..577929.014 rows=22712 loops=1)"
" SubPlan"
" -> Aggregate (cost=2420.41..2420.43 rows=1 width=19) (actual time=
25.423..25.425 rows=1 loops=22712)"
" -> Seq Scan on dummy "temp" (cost=0.00..2416.01 rows=586
width=19) (actual time=0.049..17.834 rows=2414 loops=22712)"
" Filter: ((product)::text = ($0)::text)"
"Total runtime: 578968.885 ms"
On Feb 11, 2008 9:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Linux Guru" <linux.binary@gmail.com> writes:
> > We have a large datawarehouse stored in postgres and temp tables are
> created
> > based on user query. The process of temp table creation involves
> selecting
> > data from main fact table, this includes several select and update
> > statements and one of the following update statement is having
> performance
> > issues.
>
> Try ANALYZEing the temp table before the step that's too slow.
>
> If that doesn't help, let's see EXPLAIN ANALYZE (not just EXPLAIN)
> output.
>
> 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: linux.binary@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Update with Subquery Performance
In-Reply-To: <3caa866c0802120032r7b7eb672s98d38f1351622424@mail.gmail.com>
* 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