Ben Campbell wrote:
I _think_ the reason it takes so long is that postgresql doesn't
modify rows in place - it creates an entry for the modified row and
zaps the old one. So by touching _every_ row I'm basically forcing it
to rebuild my whole database... I've got about 2 million rows in
'artic
...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Ben Campbell
Gesendet: Donnerstag, 11. Februar 2010 14:45
An: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] problems maintaining boolean columns in a large table
Timo Klecker wrote:
> could you post your trigger function? W
2010/2/10 Ben Campbell
> I settled on:
>
> CREATE TABLE needs_indexing (
> article_id integer REFERENCES article(id) PRIMARY KEY
> );
>
> The primary key-ness enforces uniqueness, and any time I want to add an
> article to the queue I just make sure I do a DELETE before the INSERT. Bound
> to be
Timo Klecker wrote:
could you post your trigger function? When you need to rebuild the index,
you could disable the trigger setting the flag if the article is modified.
This could speed up your UPDATE.
Embarrassingly, when I checked, I found that I'd never gotten around to
writing that particu
...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Ben Campbell
Gesendet: Dienstag, 9. Februar 2010 12:26
An: pgsql-general@postgresql.org
Betreff: [GENERAL] problems maintaining boolean columns in a large table
I've got a database that holds a bunch of articles in a
Richard Huxton wrote:
On 09/02/10 11:25, Ben Campbell wrote:
[I was talking about moving a "needs_indexing" flag out of a big table
into it's own table]
But my gut feeling is that the flag would be better off in it's own
table anyway, eg:
CREATE TABLE needs_indexing (
article_id integer refere
On 09/02/10 11:25, Ben Campbell wrote:
[snip]
I need to set all
those flags, but it takes _ages_ to do "UPDATE article SET
needs_indexing=true;"
[snip]
I _think_ the reason it takes so long is that postgresql doesn't modify
rows in place - it creates an entry for the modified row and zaps the
o
I've got a database that holds a bunch of articles in a table called
'article'. It has a bunch of columns, and each row might hold a few KB
of data, say.
I'm maintaining a separate fulltext database, and so I added a boolean
flag, 'needs_indexing' to my 'article' table to keep track of which
ar
