postgresql doesn't use index for primary key = foreign key

Question

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

Answer 1

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.