From: Ragnar <gnari@hive.is>
To: Arnaud Lesauvage <thewild@freesurf.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow update with simple query
Date: Wed, 13 Dec 2006 11:20:03 +0000
Message-ID: <1166008803.6369.94.camel@localhost.localdomain> (raw)
In-Reply-To: <457FDB1E.6040801@freesurf.fr>
References: <457FDB1E.6040801@freesurf.fr>
On mið, 2006-12-13 at 11:51 +0100, Arnaud Lesauvage wrote:
> Hi list !
>
> I am running a query to update the boolean field of a table based on
> another table's fields.
>
> The query is (changed names for readability):
> UPDATE t1
> SET booleanfield = (t2.field1 IN ('some', 'other') AND t2.field2 = 'Y')
> FROM t2
> WHERE t1.uid = t2.uid
>
> t2.uid is the PRIMARY KEY.
> t2 only has ~1000 rows, so I think it fits fully in memory.
> t1 as ~2.000.000 rows.
> There is an index on t1.uid also.
>
> The explain (sorry, not explain analyze available yet) is :
>
> Hash Join (cost=112.75..307410.10 rows=2019448 width=357)
> Hash Cond: ("outer".uid= "inner".uid)
> -> Seq Scan on t1 (cost=0.00..261792.01 rows=2033001 width=340)
> -> Hash (cost=110.20..110.20 rows=1020 width=53)
> -> Seq Scan on t2 (cost=0.00..110.20 rows=1020 width=53)
>
> My query has been running for more than 1.5 hour now, and it is still running.
> Nothing else is running on the server.
> There are two multicolumn-indexes on this column (both are 3-columns indexes). One of them has a
> functional column (date_trunc('month', datefield)).
>
> Do you think the problem is with the indexes ?
I guess so. are you sure about the index on t1.uid?
what are the column definitions for t1.uid and t2.uid ?
are they the same ?
you should ba able to get a plan similar to:
Merge Join (cost=0.00..43.56 rows=1000 width=11)
Merge Cond: ("outer".uid = "inner".uid)
-> Index Scan using t1i on t1 (cost=0.00..38298.39 rows=2000035
width=10)
-> Index Scan using t2i on t2 (cost=0.00..26.73 rows=1000 width=5)
what postgres version are you using ?
gnari
>
> The hardware is not great, but the database is on a RAID1 array, so its not bad either.
> I am surprised that it takes more than 3 seconds per row to be updated.
>
> Thanks for your opinion on this !
>
> --
> Arnaud
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
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, thewild@freesurf.fr
Subject: Re: Slow update with simple query
In-Reply-To: <1166008803.6369.94.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