postgresql - I don't understand explain result of a slow query -


i have slow query (> 1s). here result of explain analyze on query:

nested loop left join  (cost=0.42..32275.13 rows=36 width=257) (actual time=549.409..1106.044 rows=2 loops=1)   join filter: (answer.lt_surveyee_survey_id = lt_surveyee_survey.id)     ->  index scan using lt_surveyee_survey_id_key on lt_surveyee_survey  (cost=0.42..8.44 rows=1 width=64) (actual time=0.108..0.111 rows=1 loops=1)           index cond: (id = 'xxxxx'::citext)     ->  seq scan on answer  (cost=0.00..32266.24 rows=36 width=230) (actual time=549.285..1105.910 rows=2 loops=1)           filter: (lt_surveyee_survey_id = 'xxxxx'::citext)           rows removed filter: 825315 planning time: 0.592 ms execution time: 1106.124 ms 

the xxxxx parts of result uuid like. did not built database, have no clue right now. here query:

explain analyze select   lt_surveyee_survey.id   -- +some other fields lt_surveyee_survey   left join answer on answer.lt_surveyee_survey_id = lt_surveyee_survey.id lt_surveyee_survey.id = 'xxxxx'; 

your join causing performance drop according explain analyze output. can see there 2 different lookups, 1 took couple milliseconds, , other took half second.

the difference indicated beginning of lines: index scan , seq scan, seq short sequential, meaning of rows had checked dbms process join. reason why sequential scans occur missing index on column being checked (answer.lt_surveyee_survey_id in case).

adding index should solve performance issue.


Comments

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -