From: | Misa Simic <misa(dot)simic(at)gmail(dot)com> |
---|---|
To: | F T <oukile(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: simple update query too long |
Date: | 2011-05-13 09:40:01 |
Message-ID: | BANLkTinZbD3uVKQG1iZZPeJmK=TksA-g-Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Would it be faster if you create Partial Index on inter field (btree) where
inter > 0
and then UPDATE grille SET inter = 0 WHERE inter > 0
Kind Regards,
Misa
2011/5/9 F T <oukile(at)gmail(dot)com>
> Hi list
>
> I use PostgreSQL 8.4.4. (with Postgis 1.4)
>
> I have a simple update query that takes hours to run.
> The table is rather big (2 millions records) but it takes more than 5 hours
> to run !!
>
> The query is just :
> *UPDATE grille SET inter = 0*
>
> The explain command seems ok :
> "Seq Scan on grille50 (cost=0.00..499813.56 rows=2125456 width=494)"
>
> The table as a geometry field geom (simple, it only stores squares)
> The table définition is :
> *CREATE TABLE grille50
> (
> id integer NOT NULL,
> geom geometry,
> inter integer DEFAULT 0,
> oc1 integer,
> oc2 integer,
> occalc integer,
> CONSTRAINT grille_pkey PRIMARY KEY (id),
> CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
> CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) =
> 'POLYGON'::text OR geom IS NULL),
> CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 2154)
> )
> WITH (
> OIDS=TRUE
> );
> ALTER TABLE grille OWNER TO postgres;
> CREATE INDEX grille_geom ON grille USING gist (geom);
> CREATE INDEX grille_id ON grille USING btree (id);*
>
>
> So any ideas why is it soo long???
>
> Many thanks
>
> Fabrice
>
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | James B. Byrne | 2011-05-13 13:12:15 | Re: How to handle bogus nulls from ActiveRecord |
Previous Message | Rajesh Kumar Mallah | 2011-05-13 08:30:27 | pg_dumpall behavior in 9.1beta1 |