Storage/Performance and splitting a table
От
Craig A. James
Тема
Storage/Performance and splitting a table
Дата
Msg-id
437F66CF.7080402@modgraph-usa.com
Ответ на
Re: Perl DBD and an alarming problem (Michael Fuhr)
Список
Дерево обсуждения
Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Alex Turner <armtuk@gmail.com>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Dave Cramer <pg@fastcrypt.com>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware "Luke Lonergan" <llonergan@greenplum.com>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Steve Wampler <swampler@noao.edu>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware "Luke Lonergan" <llonergan@greenplum.com>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Perl DBD and an alarming problem "Craig A. James" <cjames@modgraph-usa.com>
Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
Re: Perl DBD and an alarming problem "Craig A. James" <cjames@modgraph-usa.com>
Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
Re: Perl DBD and an alarming problem "Greg Sabino Mullane" <greg@turnstep.com>
In a recent thread, several people pointed out that UPDATE = DELETE+INSERT. This got me to wondering.
I have a table that, roughly, looks like this:
create table doc (
id integer primary key,
document text,
keywords tsvector
);
where "keywords" has a GIST index. There are about 10 million rows in the table, and an average of 20 keywords per document. I have two questions.
First, I occasionally rebuild the keywords, after which the VACUUM FULL ANALYZE takes a LONG time - like 24 hours. Given the UPDATE = DELETE+INSERT, it sounds like I'd be better off with something like this:
create table doc (
id integer primary key,
document text,
);
create table keywords (
id integer primary key,
keywords tsvector
);
Then I could just drop the GIST index, truncate the keywords table, rebuild the keywords, and reindex. My suspicion is that VACUUM FULL ANALYZE would be quick -- there would be no garbage to collect, so all it would to do is the ANALYZE part.
My second question: With the doc and keywords split into two tables, would the tsearch2/GIST performance be faster? The second schema's "keywords" table has just pure keywords (no documents); does that translate to fewer blocks being read during a tsearch2/GIST query? Or are the "document" and "keywords" columns of the first schema already stored separately on disk so that the size of the "document" data doesn't affect the "keywords" search performance?
Thanks,
Craig
В списке pgsql-performance по дате отправления