Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- chinese=# EXPLAIN ANALYZE SELECT COUNT(sentence_word.sentence_id) FROM content JOIN sentence ON sentence.content_id=content.id JOIN sentence_word ON sentence_word.sentence_id=sentence.id WHERE content.source_type=2;
- QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Finalize Aggregate (cost=5318457.26..5318457.27 rows=1 width=8) (actual time=40038.643..40038.643 rows=1 loops=1)
- -> Gather (cost=5318457.05..5318457.26 rows=2 width=8) (actual time=40038.615..40038.638 rows=3 loops=1)
- Workers Planned: 2
- Workers Launched: 2
- -> Partial Aggregate (cost=5317457.05..5317457.06 rows=1 width=8) (actual time=40035.364..40035.364 rows=1 loops=3)
- -> Hash Join (cost=1061549.16..5310334.57 rows=2848991 width=4) (actual time=36729.241..40035.179 rows=1880 loops=3)
- Hash Cond: (sentence_word.sentence_id = sentence.id)
- -> Parallel Seq Scan on sentence_word (cost=0.00..3628067.00 rows=157927600 width=4) (actual time=0.184..19460.798 rows=126342084 loops=3)
- -> Hash (cost=1055734.37..1055734.37 rows=465183 width=4) (actual time=8257.710..8257.710 rows=1226 loops=3)
- Buckets: 524288 Batches: 1 Memory Usage: 4140kB
- -> Hash Join (cost=1869.26..1055734.37 rows=465183 width=4) (actual time=1970.901..8257.408 rows=1226 loops=3)
- Hash Cond: (sentence.content_id = content.id)
- -> Seq Scan on sentence (cost=0.00..728315.08 rows=25786408 width=8) (actual time=0.105..4871.221 rows=25771768 loops=3)
- -> Hash (cost=1853.89..1853.89 rows=1229 width=4) (actual time=20.081..20.081 rows=1246 loops=3)
- Buckets: 2048 Batches: 1 Memory Usage: 60kB
- -> Bitmap Heap Scan on content (cost=25.82..1853.89 rows=1229 width=4) (actual time=0.396..19.817 rows=1246 loops=3)
- Recheck Cond: (source_type = 2)
- Heap Blocks: exact=170
- -> Bitmap Index Scan on content_source_idx (cost=0.00..25.51 rows=1229 width=0) (actual time=0.314..0.314 rows=1246 loops=3)
- Index Cond: (source_type = 2)
- Planning time: 9.665 ms
- Execution time: 40041.929 ms
- (22 rows)
- chinese=# set enable_seqscan=off;
- SET
- chinese=# EXPLAIN ANALYZE SELECT COUNT(sentence_word.sentence_id) FROM content JOIN sentence ON sentence.content_id=content.id JOIN sentence_word ON sentence_word.sentence_id=sentence.id WHERE content.source_type=2;
- QUERY PLAN
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Finalize Aggregate (cost=32594154.72..32594154.73 rows=1 width=8) (actual time=80.898..80.898 rows=1 loops=1)
- -> Gather (cost=32594154.60..32594154.71 rows=1 width=8) (actual time=80.883..80.894 rows=2 loops=1)
- Workers Planned: 1
- Workers Launched: 1
- -> Partial Aggregate (cost=32593154.60..32593154.61 rows=1 width=8) (actual time=79.146..79.147 rows=1 loops=2)
- -> Nested Loop (cost=58.00..32583099.34 rows=4022105 width=4) (actual time=63.608..78.882 rows=2820 loops=2)
- -> Nested Loop (cost=57.42..6895590.47 rows=273637 width=4) (actual time=63.162..75.075 rows=613 loops=2)
- -> Parallel Index Scan using content_pkey on content (cost=0.29..215101.03 rows=723 width=4) (actual time=62.474..67.980 rows=623 loops=2)
- Filter: (source_type = 2)
- Rows Removed by Filter: 33440
- -> Bitmap Heap Scan on sentence (cost=57.13..9209.89 rows=3007 width=8) (actual time=0.010..0.010 rows=1 loops=1246)
- Recheck Cond: (content_id = content.id)
- Heap Blocks: exact=663
- -> Bitmap Index Scan on contentidx (cost=0.00..56.38 rows=3007 width=0) (actual time=0.003..0.003 rows=1 loops=1246)
- Index Cond: (content_id = content.id)
- -> Index Only Scan using sentenceidx on sentence_word (cost=0.57..93.55 rows=32 width=4) (actual time=0.004..0.005 rows=5 loops=1226)
- Index Cond: (sentence_id = sentence.id)
- Heap Fetches: 2991
- Planning time: 1.118 ms
- Execution time: 84.193 ms
- (20 rows)
- chinese=# \d sentence_word
- Table "public.sentence_word"
- Column | Type | Collation | Nullable | Default
- -------------+---------+-----------+----------+---------
- sentence_id | integer | | not null |
- word_id | integer | | not null |
- position | integer | | not null |
- Indexes:
- "sentenceidx" btree (sentence_id)
- "sentencewordidx" btree (word_id)
- chinese=# \d sentence
- Table "public.sentence"
- Column | Type | Collation | Nullable | Default
- ---------------------+-----------------------------+-----------+----------+--------------------------------------
- id | integer | | not null | nextval('sentence_id_seq'::regclass)
- sentence_s | text | | |
- sentence_t | text | | |
- original_script | text | | not null |
- segmentation_method | integer | | |
- content_id | integer | | |
- word_ids_json | jsonb | | |
- created | timestamp without time zone | | | CURRENT_TIMESTAMP
- updated | timestamp without time zone | | | CURRENT_TIMESTAMP
- Indexes:
- "sentence_pkey" PRIMARY KEY, btree (id)
- "contentidx" btree (content_id)
- Foreign-key constraints:
- "sentence_content_id_fkey" FOREIGN KEY (content_id) REFERENCES content(id) ON DELETE CASCADE
- chinese=# \d content
- Table "public.content"
- Column | Type | Collation | Nullable | Default
- -----------------------+-----------------------------+-----------+----------+-------------------------------------
- id | integer | | not null | nextval('content_id_seq'::regclass)
- content_s | text | | |
- content_t | text | | |
- original_script | text | | not null |
- content_translation | text | | |
- title_s | text | | |
- title_t | text | | |
- source_type | integer | | |
- source_url | text | | |
- source_uniqid | text | | |
- user_id | integer | | |
- line_splitting_method | integer | | |
- sentences_updated | timestamp without time zone | | |
- created | timestamp without time zone | | | CURRENT_TIMESTAMP
- updated | timestamp without time zone | | | CURRENT_TIMESTAMP
- has_children | boolean | | not null | false
- source_title | text | | |
- content_type | text | | |
- genre | text | | |
- date_written | timestamp without time zone | | |
- parent_content_id | integer | | |
- imdbid | integer | | |
- omdbapi_response | jsonb | | |
- imdbapi_response | jsonb | | |
- Indexes:
- "content_pkey" PRIMARY KEY, btree (id)
- "content_source_idx" btree (source_type)
- Foreign-key constraints:
- "content_parent_content_id_fkey" FOREIGN KEY (parent_content_id) REFERENCES content(id) ON DELETE CASCADE
- Referenced by:
- TABLE "content" CONSTRAINT "content_parent_content_id_fkey" FOREIGN KEY (parent_content_id) REFERENCES content(id) ON DELETE CASCADE
- TABLE "open_subtitle" CONSTRAINT "open_subtitle_content_id_fkey" FOREIGN KEY (content_id) REFERENCES content(id) ON DELETE CASCADE
- TABLE "sentence" CONSTRAINT "sentence_content_id_fkey" FOREIGN KEY (content_id) REFERENCES content(id) ON DELETE CASCADE
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
