pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Storage/Performance and splitting a table
2+ messages / 2 participants
[nested] [flat]

* Storage/Performance and splitting a table
@ 2005-11-19 17:54 Craig A. James <cjames@modgraph-usa.com>
  2005-11-19 19:03 ` Re: Storage/Performance and splitting a table Michael Stone <mstone+postgres@mathom.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Craig A. James @ 2005-11-19 17:54 UTC (permalink / raw)
  To: 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.  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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Storage/Performance and splitting a table
  2005-11-19 17:54 Storage/Performance and splitting a table Craig A. James <cjames@modgraph-usa.com>
@ 2005-11-19 19:03 ` Michael Stone <mstone+postgres@mathom.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Michael Stone @ 2005-11-19 19:03 UTC (permalink / raw)
  To: pgsql-performance

On Sat, Nov 19, 2005 at 09:54:23AM -0800, Craig A. James wrote:
>First, I occasionally rebuild the keywords, after which the VACUUM FULL 
>ANALYZE takes a LONG time - like 24 hours.

You know you just need vacuum, not vacuum full, right? 

Mike Stone



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-11-19 19:03 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-19 17:54 Storage/Performance and splitting a table Craig A. James <cjames@modgraph-usa.com>
2005-11-19 19:03 ` Michael Stone <mstone+postgres@mathom.us>

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