pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
(Re)-indexing on updates 5+ messages / 2 participants [nested] [flat]
* (Re)-indexing on updates @ 2005-08-21 18:32 Yves Vindevogel <yves.vindevogel@implements.be> 0 siblings, 1 reply; 5+ messages in thread From: Yves Vindevogel @ 2005-08-21 18:32 UTC (permalink / raw) To: pgsql-performance Hi, Say I have a table with column A, B, C, D A has a unique index on it (primary key) B and C have a normal index on it D has no index If I perform a query like update tbl set D = 'whatever' ; that should make no difference on the indexes on the other columns, right ? Or is there some kind of mechanism that does create a sort of new record, thus makes the indexes go wild. Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. Attachments:^ permalink raw reply [nested|flat] 5+ messages in threadPasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: (Re)-indexing on updates @ 2005-08-21 19:06 Jeffrey W. Baker <jwbaker@acm.org> parent: Yves Vindevogel <yves.vindevogel@implements.be> 0 siblings, 1 reply; 5+ messages in thread From: Jeffrey W. Baker @ 2005-08-21 19:06 UTC (permalink / raw) To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance On Sun, 2005-08-21 at 20:32 +0200, Yves Vindevogel wrote: > > > ______________________________________________________________________ > > Hi, > > Say I have a table with column A, B, C, D > A has a unique index on it (primary key) > B and C have a normal index on it > D has no index > > If I perform a query like update tbl set D = 'whatever' ; > that should make no difference on the indexes on the other columns, > right ? What postgresql does on update is to make a new record, so there will be two records in your table and two records in your index. You would need to vacuum the table to mark the space for the old record free, and you would need to reindex the table to shrink the index. > > Or is there some kind of mechanism that does create a sort of new > record, thus makes the indexes go wild. Yes. -jwb ^ permalink raw reply [nested|flat] 5+ messages in thread
* Fwd: (Re)-indexing on updates @ 2005-08-21 19:36 Yves Vindevogel <yves.vindevogel@implements.be> 0 siblings, 0 replies; 5+ messages in thread From: Yves Vindevogel @ 2005-08-21 19:36 UTC (permalink / raw) To: pgsql-performance I always forget that this goes to the writer itself and not to the group. > > > Ok, this is a major setback in some of my procedures. > From time to time, I must update one field in about 10% of the records. > So this will take time. > > How can I work around that ? > > Some personal opinions ... > 1) Drop indexes, run update, create indexes, vacuum > 2) Move the field to another table and use joins ? I could delete the > records when needed and add them again > > > This mechanism, of inserting a new record and marking the old one, is > that data kept somewhere where I can "see" it ? > I need for one app a trace of all my changes in the database. I have > a set of triggers to do that for the moment on each table. > Could I use that mechanism somehow to avoid my triggers ? > Any documentation on that mechanism (hacker stuff like what tables are > used) ? > Any good books on stuff like this ? I love to read and know how the > inside mechanics work. > > Tnx > > > > On 21 Aug 2005, at 21:06, Jeffrey W. Baker wrote: > >> On Sun, 2005-08-21 at 20:32 +0200, Yves Vindevogel wrote: >>> >>> >>> _____________________________________________________________________ >>> _ >>> >>> Hi, >>> >>> Say I have a table with column A, B, C, D >>> A has a unique index on it (primary key) >>> B and C have a normal index on it >>> D has no index >>> >>> If I perform a query like update tbl set D = 'whatever' ; >>> that should make no difference on the indexes on the other columns, >>> right ? >> >> What postgresql does on update is to make a new record, so there will >> be >> two records in your table and two records in your index. You would >> need >> to vacuum the table to mark the space for the old record free, and you >> would need to reindex the table to shrink the index. >> >>> >>> Or is there some kind of mechanism that does create a sort of new >>> record, thus makes the indexes go wild. >> >> Yes. >> >> -jwb >> >> >> > Met vriendelijke groeten, > Bien à vous, > Kind regards, > > Yves Vindevogel > Implements > > > > Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 > > Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 > > Web: http://www.implements.be > > First they ignore you. Then they laugh at you. Then they fight you. > Then you win. > Mahatma Ghandi. > Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. Attachments:Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image^ permalink raw reply [nested|flat] 5+ messages in threadPasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Fwd: (Re)-indexing on updates @ 2005-08-21 19:59 Yves Vindevogel <yves.vindevogel@implements.be> 0 siblings, 0 replies; 5+ messages in thread From: Yves Vindevogel @ 2005-08-21 19:59 UTC (permalink / raw) To: pgsql-performance My DB is quite simple. It holds data about printjobs that come from the windows eventlog. The data is shown on a website. I have one main table: tblPrintjobs. We add some extra data to it. Like the applicationtype, based on rules we define in other tables. When a rule changes, the updates take place (and take so long). Also, when new records are added, this takes place. For instance, rule 1 and rule 2 are changing positions in importance. (1 was before 2, now 2 before 1) The records that hold reference to rule 1 are reset to null (one field) Rule 2 is assigned, then rule 1 is assigned. What I could do is also: delete all from tblRefRules where rule is 1 insert all from tblPrintjobs that are not yet in RefRules for Rule2, then insert all for rule2 That would be a workaround for the MVCC. Not ? BTW: The good rule is: drop index, update, vacuum, create index ? I think I mistook the purpose of vacuum. If I index before the vacuum, my marked records will still be in the index ? Even if all transactions are finished ? Begin forwarded message: > From: "Jeffrey W. Baker" <jwbaker@acm.org> > Date: Sun 21 Aug 2005 21:36:16 CEST > To: Yves Vindevogel <yves.vindevogel@implements.be> > Subject: Re: [PERFORM] (Re)-indexing on updates > > On Sun, 2005-08-21 at 21:18 +0200, Yves Vindevogel wrote: >> >> >> ______________________________________________________________________ >> >> Ok, this is a major setback in some of my procedures. >> From time to time, I must update one field in about 10% of the >> records. >> So this will take time. >> >> How can I work around that ? >> >> Some personal opinions ... >> 1) Drop indexes, run update, create indexes, vacuum > > Drop index, update, vacuum, create index > > -or- > > update, vacuum, reindex > >> 2) Move the field to another table and use joins ? I could delete the >> records when needed and add them again > > I'm not familiar with your application, but you could try it and tell > us > if this works :) > >> >> This mechanism, of inserting a new record and marking the old one, is >> that data kept somewhere where I can "see" it ? > > This is MVCC: multi-version cuncurrency. The old record is kept > because > there could be an old transaction that can still see it, and cannot yet > see the updated record. And no other transaction can see your record > until you commit. The old row isn't removed until you vacuum. > >> I need for one app a trace of all my changes in the database. I have >> a set of triggers to do that for the moment on each table. >> Could I use that mechanism somehow to avoid my triggers ? >> Any documentation on that mechanism (hacker stuff like what tables are >> used) ? > > You could search the postgresql documentation (or the web) for MVCC. > > Regards, > jwb > > > Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. Attachments:^ permalink raw reply [nested|flat] 5+ messages in threadPasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: (Re)-indexing on updates @ 2005-08-22 09:11 Yves Vindevogel <yves.vindevogel@implements.be> parent: Jeffrey W. Baker <jwbaker@acm.org> 0 siblings, 0 replies; 5+ messages in thread From: Yves Vindevogel @ 2005-08-22 09:11 UTC (permalink / raw) To: Jeffrey W. Baker <jwbaker@acm.org>; +Cc: pgsql-performance The option with T1: A B C and T2 A D (to avoid the updates) works very well with a simple query Insert into T2 (A, D) select A, functionToGetD from T1 left join T2 on T1.A = T2.A where T2.A is null The above gives me the new records for those where D was not filled yet. Since they are all new records, I have no trouble with the MVCC On 21 Aug 2005, at 21:06, Jeffrey W. Baker wrote: > On Sun, 2005-08-21 at 20:32 +0200, Yves Vindevogel wrote: >> >> >> ______________________________________________________________________ >> >> Hi, >> >> Say I have a table with column A, B, C, D >> A has a unique index on it (primary key) >> B and C have a normal index on it >> D has no index >> >> If I perform a query like update tbl set D = 'whatever' ; >> that should make no difference on the indexes on the other columns, >> right ? > > What postgresql does on update is to make a new record, so there will > be > two records in your table and two records in your index. You would > need > to vacuum the table to mark the space for the old record free, and you > would need to reindex the table to shrink the index. > >> >> Or is there some kind of mechanism that does create a sort of new >> record, thus makes the indexes go wild. > > Yes. > > -jwb > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. Attachments:^ permalink raw reply [nested|flat] 5+ messages in threadPasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
end of thread, other threads:[~2005-08-22 09:11 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-08-21 18:32 (Re)-indexing on updates Yves Vindevogel <yves.vindevogel@implements.be> 2005-08-21 19:06 ` Jeffrey W. Baker <jwbaker@acm.org> 2005-08-22 09:11 ` Yves Vindevogel <yves.vindevogel@implements.be> 2005-08-21 19:36 Fwd: (Re)-indexing on updates Yves Vindevogel <yves.vindevogel@implements.be> 2005-08-21 19:59 Fwd: (Re)-indexing on updates Yves Vindevogel <yves.vindevogel@implements.be>
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