Thread: Versioned, chunked documents
Hi,
I have documents which are divided into chunks, so that the (ordered)
concatenation of chunks make the whole document. Each of the chunks may
be edited separately and past versions of the chunks need to be kept.
The structure looks fairly simple:
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
...
);
CREATE TABLE documents_chunks (
id SERIAL PRIMARY KEY,
ctime TIMESTAMP NOT NULL,
documents_id INTEGER REFERENCES documents(id),
seq INTEGER NOT NULL, -- sequence within the document
content TEXT,
...
);
The first goal is to retrieve the latest version of the whole document,
made from the latest versions of all chunks, but later the goal will
also be to fetch the whole version at some point in time (i.e. with
chunks created before a point in time).
I did the first goal by creating two helper views:
CREATE VIEW documents_chunks_last_version_chunk_ids AS
SELECT documents_id, max(id), seq FROM documents_chunks GROUP BY
documents_id, seq;
CREATE VIEW documents_chunks_last_version_content AS
SELECT documents_chunks.documents_id, content
FROM documents_chunks
JOIN documents_chunks_last_version_chunk_ids ON
documents_chunks.id=documents_chunks_last_version_chunk_ids.max
ORDER BY documents_chunks_last_version_chunk_ids.seq;
There are indexes on the document_chunks fields seq and documents_id.
Everything looked fine until I examined the output of EXPLAIN ANALYZE
and saw this:
db=> set enable_seqscan to off;
SET
db=> explain analyze select * from documents_chunks_last_version_content
where documents_id=1;
EXPLAIN output given in: http://explain.depesz.com/s/mpY
The query output seems correct on this test case:
db=> select * from documents_chunks_last_version_content where
documents_id=1;
documents_id | content
--------------+---------
1 | C1, v2
1 | C2, v3
1 | C3, v1
(3 rows)
This huge cost of 10000000000 which appeared out of nowhere in the
EXPLAIN output and the seq scan worry me - where did that come from?
There are absolutely no unindexed fields in the query, and the result
set of the aggregate ("max") is very small.
Of course, I might be doing the whole structure wrong - any ideas?
Hi, On 2 April 2012 08:38, Ivan Voras <ivoras@freebsd.org> wrote: > db=> set enable_seqscan to off; <snip> > > This huge cost of 10000000000 which appeared out of nowhere in the > EXPLAIN output and the seq scan worry me - where did that come from? It is not possible to disable seq scan completely. The "enable_seqscan to off" just sets cost of this operation extremely high (10 000 000 000) thus planner is forced look for better plan. In your case planner wasn't able to find anything better hence cost=10000000009.55..10000000009.56. -- Ondrej Ivanic (ondrej.ivanic@gmail.com)
2012/4/2 Ondrej Ivanič <ondrej.ivanic@gmail.com>: > Hi, > > On 2 April 2012 08:38, Ivan Voras <ivoras@freebsd.org> wrote: >> db=> set enable_seqscan to off; > > <snip> > >> >> This huge cost of 10000000000 which appeared out of nowhere in the >> EXPLAIN output and the seq scan worry me - where did that come from? > > It is not possible to disable seq scan completely. The "enable_seqscan > to off" just sets cost of this operation extremely high (10 000 000 > 000) thus planner is forced look for better plan. In your case planner > wasn't able to find anything better hence > cost=10000000009.55..10000000009.56. Hi, Thanks, I suspected something hard-coded might be adding to the cost, but this leaves the more important issue: why is a seq scan happening at all with an indexed field (the "id")? If I read the EXPLAIN output (at http://explain.depesz.com/s/mpY) correctly, this is where it happens: Hash Cond: ((max(public.documents_chunks.id)) = public.documents_chunks.id) The left hand side is a result of the MAX aggregate with a GROUP, but the number of records is really small so I think the index should be used on the right hand side of the hash cond.
On Mon, 2012-04-02 at 00:38 +0200, Ivan Voras wrote:
> Hi,
>
> I have documents which are divided into chunks, so that the (ordered)
> concatenation of chunks make the whole document. Each of the chunks may
> be edited separately and past versions of the chunks need to be kept.
>
> The structure looks fairly simple:
>
> The first goal is to retrieve the latest version of the whole document,
> made from the latest versions of all chunks, but later the goal will
> also be to fetch the whole version at some point in time (i.e. with
> chunks created before a point in time).
>
> I did the first goal by creating two helper views:
>
> CREATE VIEW documents_chunks_last_version_chunk_ids AS
> SELECT documents_id, max(id), seq FROM documents_chunks GROUP BY
> documents_id, seq;
>
> CREATE VIEW documents_chunks_last_version_content AS
> SELECT documents_chunks.documents_id, content
> FROM documents_chunks
> JOIN documents_chunks_last_version_chunk_ids ON
> documents_chunks.id=documents_chunks_last_version_chunk_ids.max
> ORDER BY documents_chunks_last_version_chunk_ids.seq;
>
> There are indexes on the document_chunks fields seq and documents_id.
>
> Everything looked fine until I examined the output of EXPLAIN ANALYZE
> and saw this:
>
I'm not surprised. First guess: I'd use
id (FK of documents), seq, ctime
as the prime key of document_chunk, which would work for your initial
requirement but is far too simplistic to deal with the general
requirement of retrieving a specific document version. You'd probably
need something like:
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
...
);
Create table version (
version_no serial primary key,
author var char 40,
ctime timestamp );
create table document_version (
id serial references document(id),
version_number serial references version(version_no),
primary_key (id, version_no),
);
CREATE TABLE documents_chunks (
id SERIAL references document_version(id),
version_number serial references document_version(version_number),
seq serial,
content TEXT,
primary_key(id, version_number, seq)
};
Disclaimer: this is not syntax checked or tested. It may/or may not
match your requirements, but since I haven't seen your ERD or the 3NF
you derived from it I can't offer any more applicable advice.
Martin
Correction interpolated - see below
On Mon, 2012-04-02 at 00:22 +0100, Martin Gregorie wrote:
> On Mon, 2012-04-02 at 00:38 +0200, Ivan Voras wrote:
> > Hi,
> >
> > I have documents which are divided into chunks, so that the (ordered)
> > concatenation of chunks make the whole document. Each of the chunks may
> > be edited separately and past versions of the chunks need to be kept.
> >
> > The structure looks fairly simple:
> >
> > The first goal is to retrieve the latest version of the whole document,
> > made from the latest versions of all chunks, but later the goal will
> > also be to fetch the whole version at some point in time (i.e. with
> > chunks created before a point in time).
> >
> > I did the first goal by creating two helper views:
> >
> > CREATE VIEW documents_chunks_last_version_chunk_ids AS
> > SELECT documents_id, max(id), seq FROM documents_chunks GROUP BY
> > documents_id, seq;
> >
> > CREATE VIEW documents_chunks_last_version_content AS
> > SELECT documents_chunks.documents_id, content
> > FROM documents_chunks
> > JOIN documents_chunks_last_version_chunk_ids ON
> > documents_chunks.id=documents_chunks_last_version_chunk_ids.max
> > ORDER BY documents_chunks_last_version_chunk_ids.seq;
> >
> > There are indexes on the document_chunks fields seq and documents_id.
> >
> > Everything looked fine until I examined the output of EXPLAIN ANALYZE
> > and saw this:
> >
> I'm not surprised. First guess: I'd use
> id (FK of documents), seq, ctime
>
> as the prime key of document_chunk, which would work for your initial
> requirement but is far too simplistic to deal with the general
> requirement of retrieving a specific document version. You'd probably
> need something like:
>
> CREATE TABLE documents (
> id SERIAL PRIMARY KEY,
> title TEXT NOT NULL,
> ...
> );
>
> Create table version (
> version_no serial primary key,
> author var char 40,
> ctime timestamp );
>
> create table document_version (
> id serial references document(id),
> version_number serial references version(version_no),
> primary_key (id, version_no),
> );
I used a version number in the key because a TIMESTAMP is much too fine
grained unless you're planning to obtain its value before committing all
the changed document_chunks affected by this editing session.
BTW, why use document_chunks when a text field can hold megabytes,
especially if they will be concatenated to form a complete document
which is then edited as a whole item and before being split into chunks
and saved back to the database? If the chunks represent chapters or
other logical sections that are always edited separately, why not name
them to reflect this? 'document_chunk' just sounds too arbitrary to me.
>
> CREATE TABLE documents_chunks (
> id SERIAL references document_version(id),
> version_number serial references document_version(version_number),
> seq serial,
> content TEXT,
> primary_key(id, version_number, seq)
==> should be
primary_key(id, seq, version_number) # of course!
> };
>
> Disclaimer: this is not syntax checked or tested. It may/or may not
> match your requirements, but since I haven't seen your ERD or the 3NF
> you derived from it I can't offer any more applicable advice.
Martin
On 02/04/2012 01:52, Martin Gregorie wrote: > BTW, why use document_chunks when a text field can hold megabytes, > especially if they will be concatenated to form a complete document > which is then edited as a whole item and before being split into chunks > and saved back to the database? ... because neither of your assumptions are true :) They will not hold megabytes, they will not often be concatenated, the document is never edited in whole, and I do *not* need version numbers on the whole document :)