Suppose I have two simple models, Sentences and Persons, where each Person is a reference to person mention in a Sentence. Let's say they have the following normalized schema:
CREATE TABLE sentence (id INT, doc_id TEXT, text TEXT)
CREATE TABLE person (id TEXT, sentence_id INT, name TEXT)
Let's say that a significant fraction of all my queries filter persons on doc_id. When, if ever, should I denormalize my schema and replicate doc_id as a field in Person?
CREATE TABLE sentence (id INT, doc_id TEXT, text TEXT)
CREATE TABLE person (id TEXT, sentence_id INT, doc_id TEXT, name TEXT)
If this case, would you recommend I use triggers or some other feature with postgres to ensure consistency?
Some empirical observations I have some empirical observations on a small database with the above schema; the sentence table contains 39786 rows and the person table contains 76385 rows. Below, I will report the query plan for different queries and execution times for postgres.
The main takeaways seem to be that if I create an index both on the querying field (doc_id here) and on the join columns (sentence_id), I can get comparable performance to replicating the field. Of course, it seems hard to beat creating an index on the replicated field.
Replicating doc_id in person with index:
EXPLAIN SELECT COUNT(*) FROM person WHERE doc_id = 'APW_ENG_20070910.0871.LDC2009T13';
Aggregate (cost=239.31..239.32 rows=1 width=0)
-> Bitmap Heap Scan on person (cost=4.97..239.13 rows=71 width=0)
Recheck Cond: (doc_id = 'APW_ENG_20070910.0871.LDC2009T13'::text)
-> Bitmap Index Scan on person_doc_id_idx (cost=0.00..4.95 rows=71 width=0)
Index Cond:(doc_id = 'APW_ENG_20070910.0871.LDC2009T13'::text)
SELECT COUNT(*) FROM person WHERE doc_id = 'APW_ENG_20070910.0871.LDC2009T13';
Time: 0.529 ms
Replicated doc_id in person without index
EXPLAIN SELECT COUNT(*) FROM person WHERE doc_id = 'APW_ENG_20070910.0871.LDC2009T13';
Aggregate (cost=2368.99..2369.00 rows=1 width=0)
-> Seq Scan on person (cost=0.00..2368.81 rows=71 width=0)
Filter: (doc_id = 'APW_ENG_20070910.0871.LDC2009T13'::text)
SELECT COUNT(*) FROM person WHERE doc_id = 'APW_ENG_20070910.0871.LDC2009T13';
Time: 12.619 ms
Joining on sentence, no index
EXPLAIN SELECT COUNT(*) FROM person p, sentence s WHERE p.sentence_id = s.id AND s.doc_id = 'APW_ENG_20070910.0871.LDC2009T13';
Aggregate (cost=10345.46..10345.47 rows=1 width=0)
-> Hash Join (cost=7880.61..10345.35 rows=44 width=0)
Hash Cond: (p.sentence_id = s.id)
-> Seq Scan on person p (cost=0.00..2177.85 rows=76385 width=4)
-> Hash (cost=7880.32..7880.32 rows=23 width=4)
-> Seq Scan on sentence s (cost=0.00..7880.32 rows=23 width=4)
Filter: (doc_id = 'APW_ENG_20070910.0871.LDC2009T13'::text)
SELECT COUNT(*) FROM person p, sentence s WHERE p.sentence_id = s.id AND s.doc_id = 'APW_ENG_20070910.0871.LDC2009T13';
Time: 37.783 ms
Joining on sentence, indexing only on join columns
EXPLAIN SELECT COUNT(*) FROM person p, sentence s WHERE p.sentence_id = s.id AND s.doc_id = 'APW_ENG_20070910.0871.LDC2009T13';
Aggregate (cost=10345.46..10345.47 rows=1 width=0)
-> Hash Join (cost=7880.61..10345.35 rows=44 width=0)
Hash Cond: (p.sentence_id = s.id)
-> Seq Scan on person p (cost=0.00..2177.85 rows=76385 width=4)
-> Hash (cost=7880.32..7880.32 rows=23 width=4)
-> Seq Scan on sentence s (cost=0.00..7880.32 rows=23 width=4)
Filter: (doc_id = 'APW_ENG_20070910.0871.LDC2009T13'::text)
SELECT COUNT(*) FROM person p, sentence s WHERE p.sentence_id = s.id AND s.doc_id = 'APW_ENG_20070910.0871.LDC2009T13';
Time: 37.783 ms
Joining on sentence, indexing both doc_id and join columns
EXPLAIN SELECT COUNT(*) FROM person p, sentence s WHERE p.sentence_id = s.id AND s.doc_id = 'APW_ENG_20070910.0871.LDC2009T13';
Aggregate (cost=542.06..542.07 rows=1 width=0)
-> Nested Loop (cost=0.71..541.95 rows=44 width=0)
-> Index Scan using sentence_doc_id_idx on sentence s (cost=0.41..93.38 rows=23 width=4)
Index Cond: (doc_id = 'APW_ENG_20070910.0871.LDC2009T13'::text)
-> Index Only Scan using person_sentence_id_idx on person p (cost=0.29..19.46 rows=4 width=4)
Index Cond: (sentence_id = s.id)
SELECT COUNT(*) FROM person p, sentence s WHERE p.sentence_id = s.id AND s.doc_id = 'APW_ENG_20070910.0871.LDC2009T13';
Time: 9.578 ms
برچسب: when if ever is deception permissible in journalism,when if ever is intoxication a defense to a crime,when if ever can groupthink be positive,
نویسنده: رضا رضوی