1

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)

explain analyze result

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
Improve this question
9
  • 1
    Please post your execution plan as formatted text please, no screen shots. You can also upload it to explain.depesz.com Commented Dec 15, 2017 at 9:27
  • 1
    Just added the formatted text for the execution plan. Sorry for the screen shot. Commented Dec 15, 2017 at 9:46
  • 1
    You'll have to post the whole plan, not only part of it, and well formatted so that it can be understood. Obviously a nested loop join is chosen because of an underestimate. Commented Dec 15, 2017 at 9:53
  • Appears to be an EAV model with ts_entry_entity as the bridge-table. I would put two composite indexes on it {entry_id ,entity_id } and { entity_id ,entrty_id } (and loose the id surrogate) and run VACUUM ANALYZE on all three tables. And there is a lot of date-to-string convesion goingh on, I hope this is not part of the join fields. Commented Dec 15, 2017 at 9:57
  • and the order by ts_entity.short_name is orthogonal to the join-fields.combined with the LIMIT 100 this could cause the need to retrieve all results before sorting and limiting. An index on short_name could help. and VACUUM ANALYZE Commented Dec 15, 2017 at 10:09

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.