خرید بک لینک

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, نویسنده: رضا رضوی تاريخ: يکشنبه 14 شهريور 1395 ساعت: 16:53

صفحه بندی