I have 3 main tables,
ts_entity(id,short_name,name,type_id)ts_entry_entity(id,entity_id,entry_id)ts_entry(id, ... other columns ...)
All the id columns are UUID, and have a Btree index.
ts_entry_entity.entity_id has foreign key to ts_entity.id, and also has Btree index.
ts_entry_entity.entry_id also is foreign key, and also has Btree index.
I have one SQL, like
select ts_entity.id,ts_entity.short_name,ts_entity.name,ts_entry.id, ... ts_entry.otherColumns ...
from ts_entity,ts_entry_entity,ts_entry
where ts_entity.id=ts_entry_entity.entity_id
and ts_entry_entity.entry_id=ts_entry.id
and ... ts_entry.otherColumns='xxx' ...
order by ts_entity.short_name
limit 100 offset 0
And here comes the weird thing, "ts_entry_entity.entity_id=ts_entity.id" doesn't use any indexes and it costs about 50s.
There is no where condition on table ts_entity.
My question: Why does ts_entry_entity.entity_id=ts_entity.id not use an index? Why does it cost so much time? How can I optimize the SQL?
Below is the explain analyze result.
Limit (cost=235455.31..235455.41 rows=1 width=1808) (actual
time=54590.304..54590.781 rows=100 loops=1) -> Unique
(cost=235455.31..235455.41 rows=1 width=1808) (actual
time=54590.301..54590.666 rows=100 loops=1)
-> Sort (cost=235455.31..235455.32 rows=1 width=1808) (actual time=54590.297..54590.410 rows=100 loops=1)
Sort Key: ts_entity.short_name, ts_entity.id, ts_entity.name, ts_entry_version.display_date, ts_entry.id,
(formatdate(totimestamp(ts_entry_version.display_date, '-5'::character
varying), 'MM/DD/YYYY'::charac ter varying)),
ts_entry_version.submitted_date,
(formatdate(totimestamp(ts_entry_version.submitted_date,
'-5'::character varying), 'MM/DD/YYYY'::character varying)),
ts_entry_type.name, (get_priority((ts_entry_version.prio
rity)::integer)), ts_entry_version.priority,
(get_sentiment((ts_entry_version.sentiment)::integer)),
ts_entry_version.sentiment,
(getdisplayvalue((ts_entry_version.source_id)::character varying, 0,
', '::character varying) ), ts_entry_version.source_id,
(NULLIF((ts_entry_version.title)::text, ''::text)),
ts_entry.submitted_date,
(formatdate(totimestamp(ts_entry.submitted_date, '-5'::character
varying), 'MM/DD/YYYY'::character varying)), (get
displayvalue((ts_entry_version.submitter_id)::character varying, 0, ',
'::character varying)), ts_entry_version.submitter_id,
entryadhoc_o9e2c9f871634dd3aeafe9bdced2e34f.owner_id,
(getdisplayvalue(toentityid((entryadhoc_o9
e2c9f871634dd3aeafe9bdced2e34f.value)::character varying,
'23f03fe70a16aed0d7e210357164e401'::character varying), 0, ',
'::character varying)),
(toentityid((entryadhoc_o9e2c9f871634dd3aeafe9bdced2e34f.value)::character
var ying, '23f03fe70a16aed0d7e210357164e401'::character varying)),
entryadhoc_td66ad96a9ab472db3cf1279b65baa69.owner_id,
(totimestamp((entryadhoc_td66ad96a9ab472db3cf1279b65baa69.value)::character
varying, '-5'::character vary ing)),
(formatdate(totimestamp((entryadhoc_td66ad96a9ab472db3cf1279b65baa69.value)::character
varying, '-5'::character varying), 'MM/DD/YYYY'::character varying)),
entryadhoc_z3757638d8d64373ad835c3523a6a70b.owner_id, (tot
imestamp((entryadhoc_z3757638d8d64373ad835c3523a6a70b.value)::character
varying, '-5'::character varying)),
(formatdate(totimestamp((entryadhoc_z3757638d8d64373ad835c3523a6a70b.value)::character
varying, '-5'::character va rying), 'MM/DD/YYYY'::character varying)),
entryadhoc_i0f819c1244b427794a83767eaa68e73.owner_id,
(totimestamp((entryadhoc_i0f819c1244b427794a83767eaa68e73.value)::character
varying, '-5'::character varying)), (formatdate(t
otimestamp((entryadhoc_i0f819c1244b427794a83767eaa68e73.value)::character
varying, '-5'::character varying), 'MM/DD/YYYY'::character varying)),
entryadhoc_i7f5d5035cac421daa9879c1e21ec63f.owner_id,
(getdisplayvalue(toentit
yid((entryadhoc_i7f5d5035cac421daa9879c1e21ec63f.value)::character
varying, '23f03fe70a16aed0d7e210357164e401'::character varying), 0, ',
'::character varying)),
(toentityid((entryadhoc_i7f5d5035cac421daa9879c1e21ec63f.val
ue)::character varying, '23f03fe70a16aed0d7e210357164e401'::character
varying)), entryadhoc_v7f9c1146ee24742a73b83526dc66df7.owner_id,
(NULLIF(entryadhoc_v7f9c1146ee24742a73b83526dc66df7.value, ''::text))
Sort Method: external merge Disk: 3360kB
-> Nested Loop (cost=22979.01..235455.30 rows=1 width=1808) (actual time=94.889..54532.919 rows=2846 loops=1)
Join Filter: (ts_entry_entity.entity_id = ts_entity.id)
Rows Removed by Join Filter: 34363583
-> Nested Loop (cost=22979.01..234676.15 rows=1 width=987) (actual time=78.801..2914.864 rows=2846 loops=1)
-> Nested Loop Anti Join (cost=22978.59..234675.43 rows=1 width=987) (actual
time=78.776..2867.254 rows=2846 loops=1)
-> Hash Join (cost=22978.17..63457.52 rows=258 width=987) (actual
time=78.614..2573.586 rows=2846 loops=1)
Hash Cond: (ts_entry.current_version_id = ts_entry_version.id)
-> Hash Left Join (cost=19831.38..59727.56 rows=154823 width=383) (actual
time=47.558..2391.088 rows=155061 loops=1)
Hash Cond: (ts_entry.id = entryadhoc_v7f9c1146ee24742a73b83526dc66df7.owner_id)
-> Hash Left Join (cost=16526.15..54467.69 rows=154823 width=337) (actual
time=38.534..2138.354 rows=155061 loops=1)
Hash Cond: (ts_entry.id = entryadhoc_i7f5d5035cac421daa9879c1e21ec63f.owner_id)
-> Hash Left Join (cost=13220.92..49207.82 rows=154823 width=291) (actual
time=30.462..1888.735 rows=155061 loops=1)
Hash Cond: (ts_entry.id = entryadhoc_i0f819c1244b427794a83767eaa68e73.owner_id)
-> Hash Left Join (cost=9915.69..43947.95 rows=154823 width=245) (actual
time=22.268..1640.688 rows=155061 loops=1)
Hash Cond: (ts_entry.id =
entryadhoc_z3757638d8d64373ad835c3523a6a70b.owner_id)
-> Hash Left Join (cost=6610.46..38688.08 rows=154823 width=199) (actual
time=19.612..1409.457 rows=155061 loops=1)
Hash Cond: (ts_entry.id =
entryadhoc_td66ad96a9ab472db3cf1279b65baa69.owner_id)
-> Hash Left Join (cost=3305.23..33428.21 rows=154823 width=153) (actual time=12.431..1161.689 rows=155061 loops=1)
Hash Cond: (ts_entry.id =
entryadhoc_o9e2c9f871634dd3aeafe9bdced2e34f.owner_id)
-> Seq Scan on ts_entry (cost=0.00..28168.34 rows=154823 width=107) (actual time=0.101..898.818 rows=155061 loops=1)
Filter: ((NOT is_draft) AND (class <> 2))
Rows Removed by Filter: 236596
-> Hash (cost=3292.29..3292.29 rows=1035 width=46) (actual time=12.304..12.304 rows=2846 loops=1)
Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory
Usage: 305kB
-> Bitmap Heap Scan on ts_attribute entryadhoc_o9e2c9f871634dd3aeafe9bdced2e34f (cost=40.45..3292.29
rows=1035 width=46) (actual time=1.191 ..9.030 rows=2846 loops=1)
Recheck Cond: (def_id = 'b4e9878722eb409c9fdfff3fdba582a3'::bpchar)
More details about tables:
ts_entity(id,short_name,name,type_id)ts_entry_entity(id,entity_id,entry_id)ts_entry(id,version_id)ts_entry_version(id,entry_id,submitted_date,title,submitter)ts_attribute(id,attribute_definition_id,entry_id,value)ts_attribute_definition(id,name)
As you can see, ts_entry_version will save all versions for one entry. ts_attribute is used for the extendable columns for entry.
More details about the SQL
We have several filters on ts_entry_version columns and ts_attribute.value. ts_attribute.value is varchar, but the content may be time milliseconds, normal string value, one or serval id values. The structure of the SQL is like below:
select ts_entity.short_name, ts_entry_version.title, ts_attribute.value from ts_entity, ts_entry_entity,ts_entry left join ts_attribute on ts_entry.id=ts_attribute.entry_id and ts_attribute.attribute_definition_id='xxx' where ts_entity.id=ts_entry_entity.entity_id and ts_entry_entity.entry_id=ts_entry.id and ts_entry.version_id=ts_entry_version.id and ts_entry_version.title like '%xxx%' order by ts_entity.short_name asc limit 100 offset 0
I found clue in PostgreSQL official documentation, https://www.postgresql.org/docs/current/static/runtime-config-query.html
Change the configuration and the Query Optimizer will prefer to use indexes.