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 <mike@fuhr.org>)
Ответы Re: Storage/Performance and splitting a table  (Michael Stone <mstone+postgres@mathom.us>)
Список pgsql-performance
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.
Giventhe 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
thatVACUUM 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?
Thesecond schema's "keywords" table has just pure keywords (no documents); does that translate to fewer blocks being
readduring a tsearch2/GIST query?  Or are the "document" and "keywords" columns of the first schema already stored
separatelyon disk so that the size of the "document" data doesn't affect the "keywords" search performance? 

Thanks,
Craig

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Luke Lonergan"
Дата:
Сообщение: Re: Hardware/OS recommendations for large databases (
Следующее
От: Michael Stone
Дата:
Сообщение: Re: Storage/Performance and splitting a table