Bungeni Index Performance

[GoogleCodeExporter]

This issue is a placeholder for postgresql index evaluation and improvement.

Original issue reported on code.google.com by ashok.ha...@gmail.com on 31 Aug 2011 at 1:53

[GoogleCodeExporter]

Following run on all listing pages :

EXPLAIN ANALYZE 
SELECT E'group.' || groups.type || E'.' || CAST(groups.group_id AS VARCHAR) AS 
group_principal_id, groups.group_id AS groups_group_id, groups.short_name AS 
groups_short_name, groups.full_name AS groups_full_name, groups.description AS 
groups_description, groups.status AS groups_status, groups.status_date AS 
groups_status_date, groups.start_date AS groups_start_date, groups.end_date AS 
groups_end_date, groups.type AS groups_type, groups.parent_group_id AS 
groups_parent_group_id, groups.language AS groups_language, groups.custom1 AS 
groups_custom1, groups.custom2 AS groups_custom2, groups.custom3 AS 
groups_custom3, groups.custom4 AS groups_custom4, parliaments.parliament_id AS 
parliaments_parliament_id, parliaments.election_date AS 
parliaments_election_date 
    FROM groups JOIN parliaments ON groups.group_id = parliaments.parliament_id 
    WHERE '2011-08-31'::date BETWEEN groups.start_date AND groups.end_date OR groups.start_date <= '2011-08-31'::date AND groups.end_date IS NULL

Output :

"Nested Loop  (cost=0.00..9.43 rows=1 width=974) (actual time=0.058..0.085 
rows=1 loops=1)"
"  ->  Seq Scan on groups  (cost=0.00..1.14 rows=1 width=966) (actual 
time=0.021..0.030 rows=8 loops=1)"
"        Filter: ((('2011-08-31'::date >= start_date) AND ('2011-08-31'::date 
<= end_date)) OR ((start_date <= '2011-08-31'::date) AND (end_date IS NULL)))"
"  ->  Index Scan using parliaments_pkey on parliaments  (cost=0.00..8.27 
rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=8)"
"        Index Cond: (parliaments.parliament_id = groups.group_id)"
"Total runtime: 0.192 ms"

This query will run much faster if a specific parliament_id is filtered for. 
Why isnt a specific parliament not specifified here ?

Original comment by ashok.ha...@gmail.com on 31 Aug 2011 at 2:00

[GoogleCodeExporter]

Translation lookup query : 

EXPLAIN ANALYZE SELECT translations.object_id AS translations_object_id, 
translations.object_type AS translations_object_type, translations.lang AS 
translations_lang, translations.field_name AS translations_field_name, 
translations.field_text AS translations_field_text 
    FROM translations 
    WHERE translations.object_type = E'Question' AND translations.object_id = 3088 AND translations.lang = E'en_US'

Analysis:

"Index Scan using translation_lookup_index on translations  (cost=0.00..8.27 
rows=1 width=296) (actual time=0.011..0.011 rows=0 loops=1)"
"  Index Cond: ((object_id = 3088) AND ((object_type)::text = 'Question'::text) 
AND ((lang)::text = 'en_US'::text))"
"Total runtime: 0.034 ms"

Index used. looks OK.

Original comment by ashok.ha...@gmail.com on 31 Aug 2011 at 2:10

[GoogleCodeExporter]

This archetypal query is called in the workspace : 

EXPLAIN ANALYZE SELECT parliamentary_items.parliamentary_item_id AS 
parliamentary_items_parliamentary_item_id, parliamentary_items.parliament_id AS 
parliamentary_items_parliament_id, parliamentary_items.owner_id AS 
parliamentary_items_owner_id, parliamentary_items.language AS 
parliamentary_items_language, parliamentary_items.short_name AS 
parliamentary_items_short_name, parliamentary_items.full_name AS 
parliamentary_items_full_name, parliamentary_items.body_text AS 
parliamentary_items_body_text, parliamentary_items.description AS 
parliamentary_items_description, parliamentary_items.subject AS 
parliamentary_items_subject, parliamentary_items.coverage AS 
parliamentary_items_coverage, parliamentary_items.status AS 
parliamentary_items_status, parliamentary_items.status_date AS 
parliamentary_items_status_date, parliamentary_items.registry_number AS 
parliamentary_items_registry_number, parliamentary_items.uri AS 
parliamentary_items_uri, parliamentary_items.note AS parliamentary_items_note, 
parliamentary_items.receive_notification AS 
parliamentary_items_receive_notification, parliamentary_items.type AS 
parliamentary_items_type, parliamentary_items.geolocation AS 
parliamentary_items_geolocation, parliamentary_items.custom1 AS 
parliamentary_items_custom1, parliamentary_items.custom2 AS 
parliamentary_items_custom2, parliamentary_items.custom3 AS 
parliamentary_items_custom3, parliamentary_items.custom4 AS 
parliamentary_items_custom4, parliamentary_items.timestamp AS 
parliamentary_items_timestamp, bills.bill_id AS bills_bill_id, 
bills.bill_type_id AS bills_bill_type_id, bills.ministry_id AS 
bills_ministry_id, bills.identifier AS bills_identifier, bills.publication_date 
AS bills_publication_date, users_1.user_id AS users_1_user_id, users_1.login AS 
users_1_login, users_1.titles AS users_1_titles, users_1.first_name AS 
users_1_first_name, users_1.last_name AS users_1_last_name, users_1.middle_name 
AS users_1_middle_name, users_1.email AS users_1_email, users_1.gender AS 
users_1_gender, users_1.date_of_birth AS users_1_date_of_birth, 
users_1.birth_country AS users_1_birth_country, users_1.birth_nationality AS 
users_1_birth_nationality, users_1.current_nationality AS 
users_1_current_nationality, users_1.uri AS users_1_uri, users_1.date_of_death 
AS users_1_date_of_death, users_1.type_of_id AS users_1_type_of_id, 
users_1.national_id AS users_1_national_id, users_1.password AS 
users_1_password, users_1.salt AS users_1_salt, users_1.description AS 
users_1_description, users_1.image AS users_1_image, users_1.active_p AS 
users_1_active_p, users_1.receive_notification AS users_1_receive_notification, 
users_1.language AS users_1_language 
    FROM parliamentary_items JOIN bills ON parliamentary_items.parliamentary_item_id = bills.bill_id LEFT OUTER JOIN users AS users_1 ON parliamentary_items.owner_id = users_1.user_id 
    WHERE parliamentary_items.status IN (E'submitted_signatories')

Analysis :

"Nested Loop Left Join  (cost=0.00..313.99 rows=1 width=4877) (actual 
time=2.059..2.059 rows=0 loops=1)"
"  Join Filter: (parliamentary_items.owner_id = users_1.user_id)"
"  ->  Nested Loop  (cost=0.00..312.79 rows=1 width=1204) (actual 
time=2.059..2.059 rows=0 loops=1)"
"        ->  Seq Scan on parliamentary_items  (cost=0.00..296.23 rows=2 
width=1184) (actual time=2.057..2.057 rows=0 loops=1)"
"              Filter: ((status)::text = 'submitted_signatories'::text)"
"        ->  Index Scan using bills_pkey on bills  (cost=0.00..8.27 rows=1 
width=20) (never executed)"
"              Index Cond: (bills.bill_id = 
parliamentary_items.parliamentary_item_id)"
"  ->  Seq Scan on users users_1  (cost=0.00..1.09 rows=9 width=3673) (never 
executed)"
"Total runtime: 2.198 ms"

Full table scan -- no index being hit except the primary key on the bill.

Adding an index on parliamentary_items.status :

CREATE INDEX pi_status_idx
  ON parliamentary_items
  USING btree
  (status);

"Nested Loop Left Join  (cost=0.00..29.18 rows=1 width=4877) (actual 
time=0.024..0.024 rows=0 loops=1)"
"  Join Filter: (parliamentary_items.owner_id = users_1.user_id)"
"  ->  Nested Loop  (cost=0.00..27.98 rows=1 width=1204) (actual 
time=0.023..0.023 rows=0 loops=1)"
"        ->  Index Scan using pi_status_idx on parliamentary_items  
(cost=0.00..11.42 rows=2 width=1184) (actual time=0.023..0.023 rows=0 loops=1)"
"              Index Cond: ((status)::text = 'submitted_signatories'::text)"
"        ->  Index Scan using bills_pkey on bills  (cost=0.00..8.27 rows=1 
width=20) (never executed)"
"              Index Cond: (bills.bill_id = 
parliamentary_items.parliamentary_item_id)"
"  ->  Seq Scan on users users_1  (cost=0.00..1.09 rows=9 width=3673) (never 
executed)"
"Total runtime: 0.173 ms"

the query is more than 10 times faster.

Original comment by ashok.ha...@gmail.com on 31 Aug 2011 at 2:20

[GoogleCodeExporter]

The index on the status field also improves this other archetypal query :

EXPLAIN ANALYZE SELECT parliamentary_items.parliamentary_item_id AS 
parliamentary_items_parliamentary_item_id, parliamentary_items.parliament_id AS 
parliamentary_items_parliament_id, parliamentary_items.owner_id AS 
parliamentary_items_owner_id, parliamentary_items.language AS 
parliamentary_items_language, parliamentary_items.short_name AS 
parliamentary_items_short_name, parliamentary_items.full_name AS 
parliamentary_items_full_name, parliamentary_items.body_text AS 
parliamentary_items_body_text, parliamentary_items.description AS 
parliamentary_items_description, parliamentary_items.subject AS 
parliamentary_items_subject, parliamentary_items.coverage AS 
parliamentary_items_coverage, parliamentary_items.status AS 
parliamentary_items_status, parliamentary_items.status_date AS 
parliamentary_items_status_date, parliamentary_items.registry_number AS 
parliamentary_items_registry_number, parliamentary_items.uri AS 
parliamentary_items_uri, parliamentary_items.note AS parliamentary_items_note, 
parliamentary_items.receive_notification AS 
parliamentary_items_receive_notification, parliamentary_items.type AS 
parliamentary_items_type, parliamentary_items.geolocation AS 
parliamentary_items_geolocation, parliamentary_items.custom1 AS 
parliamentary_items_custom1, parliamentary_items.custom2 AS 
parliamentary_items_custom2, parliamentary_items.custom3 AS 
parliamentary_items_custom3, parliamentary_items.custom4 AS 
parliamentary_items_custom4, parliamentary_items.timestamp AS 
parliamentary_items_timestamp, questions.question_id AS questions_question_id, 
questions.question_number AS questions_question_number, 
questions.ministry_submit_date AS questions_ministry_submit_date, 
questions.question_type_id AS questions_question_type_id, 
questions.response_type_id AS questions_response_type_id, 
questions.supplement_parent_id AS questions_supplement_parent_id, 
questions.sitting_time AS questions_sitting_time, questions.ministry_id AS 
questions_ministry_id, questions.response_text AS questions_response_text, 
response_types_1.response_type_id AS response_types_1_response_type_id, 
response_types_1.response_type_name AS response_types_1_response_type_name, 
response_types_1.language AS response_types_1_language, 
question_types_1.question_type_id AS question_types_1_question_type_id, 
question_types_1.question_type_name AS question_types_1_question_type_name, 
question_types_1.language AS question_types_1_language, E'group.' || 
groups_1.type || E'.' || CAST(groups_1.group_id AS VARCHAR) AS anon_1, 
groups_1.group_id AS groups_1_group_id, groups_1.short_name AS 
groups_1_short_name, groups_1.full_name AS groups_1_full_name, 
groups_1.description AS groups_1_description, groups_1.status AS 
groups_1_status, groups_1.status_date AS groups_1_status_date, 
groups_1.start_date AS groups_1_start_date, groups_1.end_date AS 
groups_1_end_date, groups_1.type AS groups_1_type, groups_1.parent_group_id AS 
groups_1_parent_group_id, groups_1.language AS groups_1_language, 
groups_1.custom1 AS groups_1_custom1, groups_1.custom2 AS groups_1_custom2, 
groups_1.custom3 AS groups_1_custom3, groups_1.custom4 AS groups_1_custom4, 
users_1.user_id AS users_1_user_id, users_1.login AS users_1_login, 
users_1.titles AS users_1_titles, users_1.first_name AS users_1_first_name, 
users_1.last_name AS users_1_last_name, users_1.middle_name AS 
users_1_middle_name, users_1.email AS users_1_email, users_1.gender AS 
users_1_gender, users_1.date_of_birth AS users_1_date_of_birth, 
users_1.birth_country AS users_1_birth_country, users_1.birth_nationality AS 
users_1_birth_nationality, users_1.current_nationality AS 
users_1_current_nationality, users_1.uri AS users_1_uri, users_1.date_of_death 
AS users_1_date_of_death, users_1.type_of_id AS users_1_type_of_id, 
users_1.national_id AS users_1_national_id, users_1.password AS 
users_1_password, users_1.salt AS users_1_salt, users_1.description AS 
users_1_description, users_1.image AS users_1_image, users_1.active_p AS 
users_1_active_p, users_1.receive_notification AS users_1_receive_notification, 
users_1.language AS users_1_language 
    FROM parliamentary_items JOIN questions ON parliamentary_items.parliamentary_item_id = questions.question_id LEFT OUTER JOIN response_types AS response_types_1 ON response_types_1.response_type_id = questions.response_type_id LEFT OUTER JOIN question_types AS question_types_1 ON question_types_1.question_type_id = questions.question_type_id LEFT OUTER JOIN groups AS groups_1 ON groups_1.group_id = questions.ministry_id AND groups_1.type IN (E'ministry') LEFT OUTER JOIN users AS users_1 ON parliamentary_items.owner_id = users_1.user_id 
    WHERE parliamentary_items.status IN (E'clarification_required', E'submitted_signatories', E'submitted_signatories')

Which takes :

"Nested Loop Left Join  (cost=1.10..359.12 rows=6 width=7880) (actual 
time=1.491..1.491 rows=0 loops=1)"
"  ->  Nested Loop Left Join  (cost=1.10..357.30 rows=6 width=7336) (actual 
time=1.490..1.490 rows=0 loops=1)"
"        ->  Nested Loop Left Join  (cost=1.10..355.56 rows=6 width=6792) 
(actual time=1.490..1.490 rows=0 loops=1)"
"              Join Filter: (groups_1.group_id = questions.ministry_id)"
"              ->  Nested Loop  (cost=1.10..353.85 rows=6 width=5826) (actual 
time=1.489..1.489 rows=0 loops=1)"
"                    ->  Nested Loop Left Join  (cost=1.10..304.16 rows=6 
width=4857) (actual time=1.489..1.489 rows=0 loops=1)"
"                          Join Filter: (parliamentary_items.owner_id = 
users_1.user_id)"
"                          ->  Seq Scan on parliamentary_items  
(cost=0.00..301.85 rows=6 width=1184) (actual time=1.488..1.488 rows=0 loops=1)"
"                                Filter: ((status)::text = ANY 
('{clarification_required,submitted_signatories,submitted_signatories}'::text[])
)"
"                          ->  Materialize  (cost=1.10..1.19 rows=9 width=3673) 
(never executed)"
"                                ->  Seq Scan on users users_1  
(cost=0.00..1.09 rows=9 width=3673) (never executed)"
"                    ->  Index Scan using questions_pkey on questions  
(cost=0.00..8.27 rows=1 width=969) (never executed)"
"                          Index Cond: (questions.question_id = 
parliamentary_items.parliamentary_item_id)"
"              ->  Index Scan using grp_type_id on groups groups_1  
(cost=0.00..0.27 rows=1 width=966) (never executed)"
"                    Index Cond: ((groups_1.type)::text = 'ministry'::text)"
"        ->  Index Scan using question_types_pkey on question_types 
question_types_1  (cost=0.00..0.28 rows=1 width=544) (never executed)"
"              Index Cond: (question_types_1.question_type_id = 
questions.question_type_id)"
"  ->  Index Scan using response_types_pkey on response_types response_types_1  
(cost=0.00..0.28 rows=1 width=544) (never executed)"
"        Index Cond: (response_types_1.response_type_id = 
questions.response_type_id)"
"Total runtime: 1.714 ms"

After applying the index :

"Nested Loop Left Join  (cost=14.00..90.28 rows=6 width=7880) (actual 
time=0.034..0.034 rows=0 loops=1)"
"  ->  Nested Loop Left Join  (cost=14.00..88.46 rows=6 width=7336) (actual 
time=0.034..0.034 rows=0 loops=1)"
"        ->  Nested Loop Left Join  (cost=14.00..86.72 rows=6 width=6792) 
(actual time=0.034..0.034 rows=0 loops=1)"
"              Join Filter: (groups_1.group_id = questions.ministry_id)"
"              ->  Nested Loop  (cost=14.00..85.01 rows=6 width=5826) (actual 
time=0.033..0.033 rows=0 loops=1)"
"                    ->  Hash Left Join  (cost=14.00..35.32 rows=6 width=4857) 
(actual time=0.033..0.033 rows=0 loops=1)"
"                          Hash Cond: (parliamentary_items.owner_id = 
users_1.user_id)"
"                          ->  Bitmap Heap Scan on parliamentary_items  
(cost=12.80..34.04 rows=6 width=1184) (actual time=0.033..0.033 rows=0 loops=1)"
"                                Recheck Cond: ((status)::text = ANY 
('{clarification_required,submitted_signatories,submitted_signatories}'::text[])
)"
"                                ->  Bitmap Index Scan on pi_status_idx  
(cost=0.00..12.80 rows=6 width=0) (actual time=0.031..0.031 rows=0 loops=1)"
"                                      Index Cond: ((status)::text = ANY 
('{clarification_required,submitted_signatories,submitted_signatories}'::text[])
)"
"                          ->  Hash  (cost=1.09..1.09 rows=9 width=3673) (never 
executed)"
"                                ->  Seq Scan on users users_1  
(cost=0.00..1.09 rows=9 width=3673) (never executed)"
"                    ->  Index Scan using questions_pkey on questions  
(cost=0.00..8.27 rows=1 width=969) (never executed)"
"                          Index Cond: (questions.question_id = 
parliamentary_items.parliamentary_item_id)"
"              ->  Index Scan using grp_type_id on groups groups_1  
(cost=0.00..0.27 rows=1 width=966) (never executed)"
"                    Index Cond: ((groups_1.type)::text = 'ministry'::text)"
"        ->  Index Scan using question_types_pkey on question_types 
question_types_1  (cost=0.00..0.28 rows=1 width=544) (never executed)"
"              Index Cond: (question_types_1.question_type_id = 
questions.question_type_id)"
"  ->  Index Scan using response_types_pkey on response_types response_types_1  
(cost=0.00..0.28 rows=1 width=544) (never executed)"
"        Index Cond: (response_types_1.response_type_id = 
questions.response_type_id)"
"Total runtime: 0.250 ms"

Over 6 times faster 

Original comment by ashok.ha...@gmail.com on 31 Aug 2011 at 2:29

[GoogleCodeExporter]

Another archetypal query in workspace :

EXPLAIN ANALYZE SELECT parliamentary_items.parliamentary_item_id AS 
parliamentary_items_parliamentary_item_id, parliamentary_items.parliament_id AS 
parliamentary_items_parliament_id, parliamentary_items.owner_id AS 
parliamentary_items_owner_id, parliamentary_items.language AS 
parliamentary_items_language, parliamentary_items.short_name AS 
parliamentary_items_short_name, parliamentary_items.full_name AS 
parliamentary_items_full_name, parliamentary_items.body_text AS 
parliamentary_items_body_text, parliamentary_items.description AS 
parliamentary_items_description, parliamentary_items.subject AS 
parliamentary_items_subject, parliamentary_items.coverage AS 
parliamentary_items_coverage, parliamentary_items.status AS 
parliamentary_items_status, parliamentary_items.status_date AS 
parliamentary_items_status_date, parliamentary_items.registry_number AS 
parliamentary_items_registry_number, parliamentary_items.uri AS 
parliamentary_items_uri, parliamentary_items.note AS parliamentary_items_note, 
parliamentary_items.receive_notification AS 
parliamentary_items_receive_notification, parliamentary_items.type AS 
parliamentary_items_type, parliamentary_items.geolocation AS 
parliamentary_items_geolocation, parliamentary_items.custom1 AS 
parliamentary_items_custom1, parliamentary_items.custom2 AS 
parliamentary_items_custom2, parliamentary_items.custom3 AS 
parliamentary_items_custom3, parliamentary_items.custom4 AS 
parliamentary_items_custom4, parliamentary_items.timestamp AS 
parliamentary_items_timestamp, questions.question_id AS questions_question_id, 
questions.question_number AS questions_question_number, 
questions.ministry_submit_date AS questions_ministry_submit_date, 
questions.question_type_id AS questions_question_type_id, 
questions.response_type_id AS questions_response_type_id, 
questions.supplement_parent_id AS questions_supplement_parent_id, 
questions.sitting_time AS questions_sitting_time, questions.ministry_id AS 
questions_ministry_id, questions.response_text AS questions_response_text, 
response_types_1.response_type_id AS response_types_1_response_type_id, 
response_types_1.response_type_name AS response_types_1_response_type_name, 
response_types_1.language AS response_types_1_language, 
question_types_1.question_type_id AS question_types_1_question_type_id, 
question_types_1.question_type_name AS question_types_1_question_type_name, 
question_types_1.language AS question_types_1_language, E'group.' || 
groups_1.type || E'.' || CAST(groups_1.group_id AS VARCHAR) AS anon_1, 
groups_1.group_id AS groups_1_group_id, groups_1.short_name AS 
groups_1_short_name, groups_1.full_name AS groups_1_full_name, 
groups_1.description AS groups_1_description, groups_1.status AS 
groups_1_status, groups_1.status_date AS groups_1_status_date, 
groups_1.start_date AS groups_1_start_date, groups_1.end_date AS 
groups_1_end_date, groups_1.type AS groups_1_type, groups_1.parent_group_id AS 
groups_1_parent_group_id, groups_1.language AS groups_1_language, 
groups_1.custom1 AS groups_1_custom1, groups_1.custom2 AS groups_1_custom2, 
groups_1.custom3 AS groups_1_custom3, groups_1.custom4 AS groups_1_custom4, 
users_1.user_id AS users_1_user_id, users_1.login AS users_1_login, 
users_1.titles AS users_1_titles, users_1.first_name AS users_1_first_name, 
users_1.last_name AS users_1_last_name, users_1.middle_name AS 
users_1_middle_name, users_1.email AS users_1_email, users_1.gender AS 
users_1_gender, users_1.date_of_birth AS users_1_date_of_birth, 
users_1.birth_country AS users_1_birth_country, users_1.birth_nationality AS 
users_1_birth_nationality, users_1.current_nationality AS 
users_1_current_nationality, users_1.uri AS users_1_uri, users_1.date_of_death 
AS users_1_date_of_death, users_1.type_of_id AS users_1_type_of_id, 
users_1.national_id AS users_1_national_id, users_1.password AS 
users_1_password, users_1.salt AS users_1_salt, users_1.description AS 
users_1_description, users_1.image AS users_1_image, users_1.active_p AS 
users_1_active_p, users_1.receive_notification AS users_1_receive_notification, 
users_1.language AS users_1_language 
    FROM parliamentary_items JOIN questions ON parliamentary_items.parliamentary_item_id = questions.question_id LEFT OUTER JOIN response_types AS response_types_1 ON response_types_1.response_type_id = questions.response_type_id LEFT OUTER JOIN question_types AS question_types_1 ON question_types_1.question_type_id = questions.question_type_id LEFT OUTER JOIN groups AS groups_1 ON groups_1.group_id = questions.ministry_id AND groups_1.type IN (E'ministry') LEFT OUTER JOIN users AS users_1 ON parliamentary_items.owner_id = users_1.user_id 
    WHERE parliamentary_items.status IN (E'submitted', E'received', E'admissible', E'response_submitted')   

Analysis :

"Hash Left Join  (cost=18.07..1555.94 rows=1008 width=7880) (actual 
time=0.082..10.408 rows=1047 loops=1)"
"  Hash Cond: (questions.response_type_id = response_types_1.response_type_id)"
"  ->  Hash Left Join  (cost=17.03..1528.43 rows=1008 width=7336) (actual 
time=0.067..7.955 rows=1047 loops=1)"
"        Hash Cond: (questions.question_type_id = 
question_types_1.question_type_id)"
"        ->  Hash Left Join  (cost=3.88..1501.42 rows=1008 width=6792) (actual 
time=0.061..7.357 rows=1047 loops=1)"
"              Hash Cond: (questions.ministry_id = groups_1.group_id)"
"              ->  Hash Left Join  (cost=2.77..1495.27 rows=1008 width=5826) 
(actual time=0.049..6.596 rows=1047 loops=1)"
"                    Hash Cond: (parliamentary_items.owner_id = 
users_1.user_id)"
"                    ->  Merge Join  (cost=1.56..1480.21 rows=1008 width=2153) 
(actual time=0.032..5.756 rows=1047 loops=1)"
"                          Merge Cond: 
(parliamentary_items.parliamentary_item_id = questions.question_id)"
"                          ->  Index Scan using parliamentary_items_pkey on 
parliamentary_items  (cost=0.00..786.68 rows=1009 width=1184) (actual 
time=0.020..3.177 rows=1047 loops=1)"
"                                Filter: ((status)::text = ANY 
('{submitted,received,admissible,response_submitted}'::text[]))"
"                          ->  Index Scan using questions_pkey on questions  
(cost=0.00..669.84 rows=4492 width=969) (actual time=0.006..1.432 rows=3107 
loops=1)"
"                    ->  Hash  (cost=1.09..1.09 rows=9 width=3673) (actual 
time=0.009..0.009 rows=9 loops=1)"
"                          ->  Seq Scan on users users_1  (cost=0.00..1.09 
rows=9 width=3673) (actual time=0.003..0.004 rows=9 loops=1)"
"              ->  Hash  (cost=1.10..1.10 rows=1 width=966) (actual 
time=0.008..0.008 rows=1 loops=1)"
"                    ->  Seq Scan on groups groups_1  (cost=0.00..1.10 rows=1 
width=966) (actual time=0.004..0.005 rows=1 loops=1)"
"                          Filter: ((type)::text = 'ministry'::text)"
"        ->  Hash  (cost=11.40..11.40 rows=140 width=544) (actual 
time=0.002..0.002 rows=2 loops=1)"
"              ->  Seq Scan on question_types question_types_1  
(cost=0.00..11.40 rows=140 width=544) (actual time=0.001..0.001 rows=2 loops=1)"
"  ->  Hash  (cost=1.02..1.02 rows=2 width=544) (actual time=0.001..0.001 
rows=2 loops=1)"
"        ->  Seq Scan on response_types response_types_1  (cost=0.00..1.02 
rows=2 width=544) (actual time=0.001..0.001 rows=2 loops=1)"
"Total runtime: 10.656 ms"

The groups table condition results in a sequential match. If the groups PK 
included "type" instead of just group_id, this query may improve significantly.

Original comment by ashok.ha...@gmail.com on 31 Aug 2011 at 2:44

[GoogleCodeExporter]

Odd queries that will never match anything --- 
e.g. #1 

""
SELECT  E'group.' || groups.type || E'.' || CAST(groups.group_id AS VARCHAR) AS 
group_principal_id, groups.group_id AS groups_group_id, groups.short_name AS 
groups_short_name, groups.full_name AS groups_full_name, groups.description AS 
groups_description, groups.status AS groups_status, groups.status_date AS 
groups_status_date, groups.start_date AS groups_start_date, groups.end_date AS 
groups_end_date, groups.type AS groups_type, groups.parent_group_id AS 
groups_parent_group_id, groups.language AS groups_language, groups.custom1 AS 
groups_custom1, groups.custom2 AS groups_custom2, groups.custom3 AS 
groups_custom3, groups.custom4 AS groups_custom4 
    FROM groups 
    WHERE E'group.' || groups.type || E'.' || CAST(groups.group_id AS VARCHAR) = E'zope.Authenticated' 
""

e.g. #2 

""
SELECT  E'group.' || groups.type || E'.' || CAST(groups.group_id AS VARCHAR) AS 
group_principal_id, groups.group_id AS groups_group_id, groups.short_name AS 
groups_short_name, groups.full_name AS groups_full_name, groups.description AS 
groups_description, groups.status AS groups_status, groups.status_date AS 
groups_status_date, groups.start_date AS groups_start_date, groups.end_date AS 
groups_end_date, groups.type AS groups_type, groups.parent_group_id AS 
groups_parent_group_id, groups.language AS groups_language, groups.custom1 AS 
groups_custom1, groups.custom2 AS groups_custom2, groups.custom3 AS 
groups_custom3, groups.custom4 AS groups_custom4 
    FROM groups 
    WHERE E'group.' || groups.type || E'.' || CAST(groups.group_id AS VARCHAR) = E'zope.anybody' 
""

Original comment by ashok.ha...@gmail.com on 31 Aug 2011 at 3:07

[GoogleCodeExporter]

This query (from the workspace load):

EXPLAIN ANALYZE  SELECT user_group_memberships.membership_id AS 
user_group_memberships_membership_id, user_group_memberships.user_id AS 
user_group_memberships_user_id, user_group_memberships.group_id AS 
user_group_memberships_group_id, user_group_memberships.start_date AS 
user_group_memberships_start_date, user_group_memberships.end_date AS 
user_group_memberships_end_date, user_group_memberships.notes AS 
user_group_memberships_notes, user_group_memberships.active_p AS 
user_group_memberships_active_p, user_group_memberships.replaced_id AS 
user_group_memberships_replaced_id, user_group_memberships.substitution_type AS 
user_group_memberships_substitution_type, 
user_group_memberships.membership_type AS 
user_group_memberships_membership_type, user_group_memberships.language AS 
user_group_memberships_language, E'group.' || groups_1.type || E'.' || 
CAST(groups_1.group_id AS VARCHAR) AS anon_1, groups_1.group_id AS 
groups_1_group_id, groups_1.short_name AS groups_1_short_name, 
groups_1.full_name AS groups_1_full_name, groups_1.description AS 
groups_1_description, groups_1.status AS groups_1_status, groups_1.status_date 
AS groups_1_status_date, groups_1.start_date AS groups_1_start_date, 
groups_1.end_date AS groups_1_end_date, groups_1.type AS groups_1_type, 
groups_1.parent_group_id AS groups_1_parent_group_id, groups_1.language AS 
groups_1_language, groups_1.custom1 AS groups_1_custom1, groups_1.custom2 AS 
groups_1_custom2, groups_1.custom3 AS groups_1_custom3, groups_1.custom4 AS 
groups_1_custom4 
    FROM user_group_memberships LEFT OUTER JOIN groups AS groups_1 ON user_group_memberships.group_id = groups_1.group_id 
    WHERE user_group_memberships.user_id = 9 AND user_group_memberships.active_p = true

Analysis:

"Nested Loop Left Join  (cost=0.00..2.34 rows=1 width=1343) (actual 
time=0.023..0.024 rows=1 loops=1)"
"  Join Filter: (user_group_memberships.group_id = groups_1.group_id)"
"  ->  Seq Scan on user_group_memberships  (cost=0.00..1.15 rows=1 width=377) 
(actual time=0.011..0.011 rows=1 loops=1)"
"        Filter: (active_p AND (user_id = 9))"
"  ->  Seq Scan on groups groups_1  (cost=0.00..1.08 rows=8 width=966) (actual 
time=0.001..0.002 rows=8 loops=1)"
"Total runtime: 0.066 ms"

Table scan all the way -- perhaps the PK on this table needs to include group 
and users.

Original comment by ashok.ha...@gmail.com on 31 Aug 2011 at 3:23

[GoogleCodeExporter]

User delegations query :

EXPLAIN ANALYZE  SELECT user_delegations.user_id AS user_delegations_user_id, 
user_delegations.delegation_id AS user_delegations_delegation_id 
    FROM user_delegations 
    WHERE user_delegations.delegation_id = 9

Analysis :

"Bitmap Heap Scan on user_delegations  (cost=20.30..30.97 rows=11 width=8) 
(actual time=0.013..0.013 rows=0 loops=1)"
"  Recheck Cond: (delegation_id = 9)"
"  ->  Bitmap Index Scan on user_delegations_pkey  (cost=0.00..20.30 rows=11 
width=0) (actual time=0.011..0.011 rows=0 loops=1)"
"        Index Cond: (delegation_id = 9)"
"Total runtime: 0.059 ms"

Index scan,  OK.

Original comment by ashok.ha...@gmail.com on 31 Aug 2011 at 3:24

[GoogleCodeExporter]

Related to comment # 6 : 

this query is being triggered via a domain property on Group :

from orm.py ---

mapper(domain.Group, schema.groups,
   primary_key=[schema.groups.c.group_id],
   properties={
       "members": relation(domain.GroupMembership),
       "group_principal_id": column_property(
           #
           # !+ ATTENTION: the following sqlalchemy str concat (on c.type)
           # gives some VERY strange behaviour :
           #
           # print "group." + schema.groups.c.type + "."
           # >>> :type_1 || groups.type || :param_1
           #
           # print group.groups.type.
           # >>> "group.%s." % (schema.groups.c.type)
           #
           ("group." + schema.groups.c.type + "." +
            rdb.cast(schema.groups.c.group_id, rdb.String)
           ).label("group_principal_id")
       ),
       "contained_groups": relation(domain.Group,
           backref=backref("parent_group",
               remote_side=schema.groups.c.group_id)
       ),
       "group_addresses": relation(domain.GroupAddress),
       # "keywords": relation(domain.Keyword, secondary=schema.groups_keywords)
   },
   polymorphic_on=schema.groups.c.type,
   polymorphic_identity="group"
)

notice that the group_principal_id is a computed property :

   "group_principal_id": column_property(
           ("group." + schema.groups.c.type + "." +
            rdb.cast(schema.groups.c.group_id, rdb.String)
           ).label("group_principal_id")
       ),

Which gets translated into this SQL :

WHERE E'group.' || groups.type || E'.' || CAST(groups.group_id AS
VARCHAR) = E'zope.Authenticated'

The problem is even for valid cases where this query is called (and
its called very many times ) -- it can never use an index because of
the computed value ... but there is no reason whatsoever to do this
computation --- the info for this is in the db -- we could as well add
a computed column to the groups table which caches this value ...and
then this property definition is no longer required ....

Original comment by ashok.ha...@gmail.com on 1 Sep 2011 at 10:42

[GoogleCodeExporter]

No usage of row limiting -- 

The below query returns 1547 matches -- 

SELECT parliamentary_items.parliamentary_item_id AS 
parliamentary_items_parliamentary_item_id, parliamentary_items.parliament_id AS 
parliamentary_items_parliament_id, parliamentary_items.owner_id AS 
parliamentary_items_owner_id, parliamentary_items.language AS 
parliamentary_items_language, parliamentary_items.short_name AS 
parliamentary_items_short_name, parliamentary_items.full_name AS 
parliamentary_items_full_name, parliamentary_items.body_text AS 
parliamentary_items_body_text, parliamentary_items.description AS 
parliamentary_items_description, parliamentary_items.subject AS 
parliamentary_items_subject, parliamentary_items.coverage AS 
parliamentary_items_coverage, parliamentary_items.status AS 
parliamentary_items_status, parliamentary_items.status_date AS 
parliamentary_items_status_date, parliamentary_items.registry_number AS 
parliamentary_items_registry_number, parliamentary_items.uri AS 
parliamentary_items_uri, parliamentary_items.note AS parliamentary_items_note, 
parliamentary_items.receive_notification AS 
parliamentary_items_receive_notification, parliamentary_items.type AS 
parliamentary_items_type, parliamentary_items.geolocation AS 
parliamentary_items_geolocation, parliamentary_items.custom1 AS 
parliamentary_items_custom1, parliamentary_items.custom2 AS 
parliamentary_items_custom2, parliamentary_items.custom3 AS 
parliamentary_items_custom3, parliamentary_items.custom4 AS 
parliamentary_items_custom4, parliamentary_items.timestamp AS 
parliamentary_items_timestamp, questions.question_id AS questions_question_id, 
questions.question_number AS questions_question_number, 
questions.ministry_submit_date AS questions_ministry_submit_date, 
questions.question_type_id AS questions_question_type_id, 
questions.response_type_id AS questions_response_type_id, 
questions.supplement_parent_id AS questions_supplement_parent_id, 
questions.sitting_time AS questions_sitting_time, questions.ministry_id AS 
questions_ministry_id, questions.response_text AS questions_response_text, 
response_types_1.response_type_id AS response_types_1_response_type_id, 
response_types_1.response_type_name AS response_types_1_response_type_name, 
response_types_1.language AS response_types_1_language, 
question_types_1.question_type_id AS question_types_1_question_type_id, 
question_types_1.question_type_name AS question_types_1_question_type_name, 
question_types_1.language AS question_types_1_language, E'group.' || 
groups_1.type || E'.' || CAST(groups_1.group_id AS VARCHAR) AS anon_1, 
groups_1.group_id AS groups_1_group_id, groups_1.short_name AS 
groups_1_short_name, groups_1.full_name AS groups_1_full_name, 
groups_1.description AS groups_1_description, groups_1.status AS 
groups_1_status, groups_1.status_date AS groups_1_status_date, 
groups_1.start_date AS groups_1_start_date, groups_1.end_date AS 
groups_1_end_date, groups_1.type AS groups_1_type, groups_1.parent_group_id AS 
groups_1_parent_group_id, groups_1.language AS groups_1_language, 
groups_1.custom1 AS groups_1_custom1, groups_1.custom2 AS groups_1_custom2, 
groups_1.custom3 AS groups_1_custom3, groups_1.custom4 AS groups_1_custom4, 
users_1.user_id AS users_1_user_id, users_1.login AS users_1_login, 
users_1.titles AS users_1_titles, users_1.first_name AS users_1_first_name, 
users_1.last_name AS users_1_last_name, users_1.middle_name AS 
users_1_middle_name, users_1.email AS users_1_email, users_1.gender AS 
users_1_gender, users_1.date_of_birth AS users_1_date_of_birth, 
users_1.birth_country AS users_1_birth_country, users_1.birth_nationality AS 
users_1_birth_nationality, users_1.current_nationality AS 
users_1_current_nationality, users_1.uri AS users_1_uri, users_1.date_of_death 
AS users_1_date_of_death, users_1.type_of_id AS users_1_type_of_id, 
users_1.national_id AS users_1_national_id, users_1.password AS 
users_1_password, users_1.salt AS users_1_salt, users_1.description AS 
users_1_description, users_1.image AS users_1_image, users_1.active_p AS 
users_1_active_p, users_1.receive_notification AS users_1_receive_notification, 
users_1.language AS users_1_language 
    FROM parliamentary_items JOIN questions ON parliamentary_items.parliamentary_item_id = questions.question_id LEFT OUTER JOIN response_types AS response_types_1 ON response_types_1.response_type_id = questions.response_type_id LEFT OUTER JOIN question_types AS question_types_1 ON question_types_1.question_type_id = questions.question_type_id LEFT OUTER JOIN groups AS groups_1 ON groups_1.group_id = questions.ministry_id AND groups_1.type IN (E'ministry') LEFT OUTER JOIN users AS users_1 ON parliamentary_items.owner_id = users_1.user_id 
    WHERE parliamentary_items.status IN (E'submitted', E'received', E'admissible', E'response_submitted')

Analysis :

"Hash Left Join  (cost=18.07..1555.94 rows=1008 width=7880) (actual 
time=0.093..14.490 rows=1047 loops=1)"
"  Hash Cond: (questions.response_type_id = response_types_1.response_type_id)"
"  ->  Hash Left Join  (cost=17.03..1528.43 rows=1008 width=7336) (actual 
time=0.076..10.747 rows=1047 loops=1)"
"        Hash Cond: (questions.question_type_id = 
question_types_1.question_type_id)"
"        ->  Hash Left Join  (cost=3.88..1501.42 rows=1008 width=6792) (actual 
time=0.069..9.155 rows=1047 loops=1)"
"              Hash Cond: (questions.ministry_id = groups_1.group_id)"
"              ->  Hash Left Join  (cost=2.77..1495.27 rows=1008 width=5826) 
(actual time=0.055..8.130 rows=1047 loops=1)"
"                    Hash Cond: (parliamentary_items.owner_id = 
users_1.user_id)"
"                    ->  Merge Join  (cost=1.56..1480.21 rows=1008 width=2153) 
(actual time=0.035..7.019 rows=1047 loops=1)"
"                          Merge Cond: 
(parliamentary_items.parliamentary_item_id = questions.question_id)"
"                          ->  Index Scan using parliamentary_items_pkey on 
parliamentary_items  (cost=0.00..786.68 rows=1009 width=1184) (actual 
time=0.023..3.809 rows=1047 loops=1)"
"                                Filter: ((status)::text = ANY 
('{submitted,received,admissible,response_submitted}'::text[]))"
"                          ->  Index Scan using questions_pkey on questions  
(cost=0.00..669.84 rows=4492 width=969) (actual time=0.008..1.782 rows=3107 
loops=1)"
"                    ->  Hash  (cost=1.09..1.09 rows=9 width=3673) (actual 
time=0.010..0.010 rows=9 loops=1)"
"                          ->  Seq Scan on users users_1  (cost=0.00..1.09 
rows=9 width=3673) (actual time=0.003..0.005 rows=9 loops=1)"
"              ->  Hash  (cost=1.10..1.10 rows=1 width=966) (actual 
time=0.009..0.009 rows=1 loops=1)"
"                    ->  Seq Scan on groups groups_1  (cost=0.00..1.10 rows=1 
width=966) (actual time=0.005..0.006 rows=1 loops=1)"
"                          Filter: ((type)::text = 'ministry'::text)"
"        ->  Hash  (cost=11.40..11.40 rows=140 width=544) (actual 
time=0.002..0.002 rows=2 loops=1)"
"              ->  Seq Scan on question_types question_types_1  
(cost=0.00..11.40 rows=140 width=544) (actual time=0.001..0.001 rows=2 loops=1)"
"  ->  Hash  (cost=1.02..1.02 rows=2 width=544) (actual time=0.002..0.002 
rows=2 loops=1)"
"        ->  Seq Scan on response_types response_types_1  (cost=0.00..1.02 
rows=2 width=544) (actual time=0.001..0.001 rows=2 loops=1)"
"Total runtime: 14.794 ms"

However only 25 rows are ever shown to the user -- 

If we use query limiting , by just adding " limit 25" at the end , the query is 
about 4 times faster :

"Limit  (cost=4.92..49.99 rows=25 width=7880) (actual time=0.129..2.827 rows=25 
loops=1)"
"  ->  Nested Loop Left Join  (cost=4.92..1822.02 rows=1008 width=7880) (actual 
time=0.128..2.823 rows=25 loops=1)"
"        ->  Hash Left Join  (cost=4.92..1516.33 rows=1008 width=7336) (actual 
time=0.104..2.704 rows=25 loops=1)"
"              Hash Cond: (questions.response_type_id = 
response_types_1.response_type_id)"
"              ->  Hash Left Join  (cost=3.88..1501.42 rows=1008 width=6792) 
(actual time=0.091..2.672 rows=25 loops=1)"
"                    Hash Cond: (questions.ministry_id = groups_1.group_id)"
"                    ->  Hash Left Join  (cost=2.77..1495.27 rows=1008 
width=5826) (actual time=0.073..2.637 rows=25 loops=1)"
"                          Hash Cond: (parliamentary_items.owner_id = 
users_1.user_id)"
"                          ->  Merge Join  (cost=1.56..1480.21 rows=1008 
width=2153) (actual time=0.046..2.590 rows=25 loops=1)"
"                                Merge Cond: 
(parliamentary_items.parliamentary_item_id = questions.question_id)"
"                                ->  Index Scan using parliamentary_items_pkey 
on parliamentary_items  (cost=0.00..786.68 rows=1009 width=1184) (actual 
time=0.028..1.527 rows=25 loops=1)"
"                                      Filter: ((status)::text = ANY 
('{submitted,received,admissible,response_submitted}'::text[]))"
"                                ->  Index Scan using questions_pkey on 
questions  (cost=0.00..669.84 rows=4492 width=969) (actual time=0.012..0.829 
rows=2024 loops=1)"
"                          ->  Hash  (cost=1.09..1.09 rows=9 width=3673) 
(actual time=0.015..0.015 rows=9 loops=1)"
"                                ->  Seq Scan on users users_1  
(cost=0.00..1.09 rows=9 width=3673) (actual time=0.004..0.005 rows=9 loops=1)"
"                    ->  Hash  (cost=1.10..1.10 rows=1 width=966) (actual 
time=0.012..0.012 rows=1 loops=1)"
"                          ->  Seq Scan on groups groups_1  (cost=0.00..1.10 
rows=1 width=966) (actual time=0.006..0.008 rows=1 loops=1)"
"                                Filter: ((type)::text = 'ministry'::text)"
"              ->  Hash  (cost=1.02..1.02 rows=2 width=544) (actual 
time=0.004..0.004 rows=2 loops=1)"
"                    ->  Seq Scan on response_types response_types_1  
(cost=0.00..1.02 rows=2 width=544) (actual time=0.002..0.002 rows=2 loops=1)"
"        ->  Index Scan using question_types_pkey on question_types 
question_types_1  (cost=0.00..0.28 rows=1 width=544) (actual time=0.001..0.002 
rows=1 loops=25)"
"              Index Cond: (question_types_1.question_type_id = 
questions.question_type_id)"
"Total runtime: 3.090 ms"

But since the results are paged -- we need the overall count -- so the way to 
reimplement it would be to first do a count of the matching results:
(We dont return the columns ) 

SELECT  Count(*)
FROM parliamentary_items JOIN questions ON 
parliamentary_items.parliamentary_item_id = questions.question_id LEFT OUTER 
JOIN response_types AS response_types_1 ON response_types_1.response_type_id = 
questions.response_type_id LEFT OUTER JOIN question_types AS question_types_1 
ON question_types_1.question_type_id = questions.question_type_id LEFT OUTER 
JOIN groups AS groups_1 ON groups_1.group_id = questions.ministry_id AND 
groups_1.type IN (E'ministry') LEFT OUTER JOIN users AS users_1 ON 
parliamentary_items.owner_id = users_1.user_id 
    WHERE parliamentary_items.status IN (E'submitted', E'received', E'admissible', E'response_submitted')

"Aggregate  (cost=714.06..714.07 rows=1 width=0) (actual time=6.621..6.621 
rows=1 loops=1)"
"  ->  Hash Left Join  (cost=321.07..711.54 rows=1008 width=0) (actual 
time=2.692..6.510 rows=1047 loops=1)"
"        Hash Cond: (questions.response_type_id = 
response_types_1.response_type_id)"
"        ->  Hash Left Join  (cost=320.03..696.63 rows=1008 width=4) (actual 
time=2.682..6.119 rows=1047 loops=1)"
"              Hash Cond: (questions.question_type_id = 
question_types_1.question_type_id)"
"              ->  Hash Left Join  (cost=306.88..669.62 rows=1008 width=8) 
(actual time=2.672..5.745 rows=1047 loops=1)"
"                    Hash Cond: (questions.ministry_id = groups_1.group_id)"
"                    ->  Hash Left Join  (cost=305.77..663.47 rows=1008 
width=12) (actual time=2.655..5.361 rows=1047 loops=1)"
"                          Hash Cond: (parliamentary_items.owner_id = 
users_1.user_id)"
"                          ->  Hash Join  (cost=304.56..648.41 rows=1008 
width=16) (actual time=2.632..4.906 rows=1047 loops=1)"
"                                Hash Cond: (questions.question_id = 
parliamentary_items.parliamentary_item_id)"
"                                ->  Seq Scan on questions  (cost=0.00..316.92 
rows=4492 width=16) (actual time=0.010..0.997 rows=4492 loops=1)"
"                                ->  Hash  (cost=291.95..291.95 rows=1009 
width=8) (actual time=2.602..2.602 rows=1047 loops=1)"
"                                      ->  Bitmap Heap Scan on 
parliamentary_items  (cost=36.82..291.95 rows=1009 width=8) (actual 
time=0.569..1.325 rows=1047 loops=1)"
"                                            Recheck Cond: ((status)::text = 
ANY ('{submitted,received,admissible,response_submitted}'::text[]))"
"                                            ->  Bitmap Index Scan on 
pi_status_idx  (cost=0.00..36.56 rows=1009 width=0) (actual time=0.496..0.496 
rows=1047 loops=1)"
"                                                  Index Cond: ((status)::text 
= ANY ('{submitted,received,admissible,response_submitted}'::text[]))"
"                          ->  Hash  (cost=1.09..1.09 rows=9 width=4) (actual 
time=0.009..0.009 rows=9 loops=1)"
"                                ->  Seq Scan on users users_1  
(cost=0.00..1.09 rows=9 width=4) (actual time=0.004..0.006 rows=9 loops=1)"
"                    ->  Hash  (cost=1.10..1.10 rows=1 width=4) (actual 
time=0.010..0.010 rows=1 loops=1)"
"                          ->  Seq Scan on groups groups_1  (cost=0.00..1.10 
rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)"
"                                Filter: ((type)::text = 'ministry'::text)"
"              ->  Hash  (cost=11.40..11.40 rows=140 width=4) (actual 
time=0.004..0.004 rows=2 loops=1)"
"                    ->  Seq Scan on question_types question_types_1  
(cost=0.00..11.40 rows=140 width=4) (actual time=0.003..0.004 rows=2 loops=1)"
"        ->  Hash  (cost=1.02..1.02 rows=2 width=4) (actual time=0.003..0.003 
rows=2 loops=1)"
"              ->  Seq Scan on response_types response_types_1  
(cost=0.00..1.02 rows=2 width=4) (actual time=0.002..0.002 rows=2 loops=1)"
"Total runtime: 6.796 ms"

Its less than half the speed

and then use limit and offset to paginate the results.

Original comment by ashok.ha...@gmail.com on 1 Sep 2011 at 12:03

[GoogleCodeExporter]

On listing pages -- e.g. listing of questions in business->questions, the 
changes table is queried (why ?? ) :

SELECT question_changes.change_id AS question_changes_change_id, 
question_changes.content_id AS question_changes_content_id, 
question_changes.action AS question_changes_action, question_changes.date_audit 
AS question_changes_date_audit, question_changes.date_active AS 
question_changes_date_active, question_changes.description AS 
question_changes_description, question_changes.notes AS question_changes_notes, 
question_changes.user_id AS question_changes_user_id 
    FROM question_changes 
    WHERE 57 = question_changes.content_id

There are no indexes -- this an expensive query :

"Seq Scan on question_changes  (cost=0.00..2533.71 rows=19 width=129) (actual 
time=0.491..23.639 rows=25 loops=1)"
"  Filter: (57 = content_id)"
"Total runtime: 23.685 ms"

With a btree index on content_id :

CREATE INDEX qchanges_contentid_idx
  ON question_changes
  USING btree
  (content_id);

the same query is over 200 times faster --

"Index Scan using qchanges_contentid_idx on question_changes  (cost=0.00..8.59 
rows=19 width=129) (actual time=0.026..0.047 rows=25 loops=1)"
"  Index Cond: (57 = content_id)"
"Total runtime: 0.095 ms"

Original comment by ashok.ha...@gmail.com on 2 Sep 2011 at 6:55

[GoogleCodeExporter]

Possible eager loading issue in listings :

there is the below query being run -- against the owner of every item being 
shown in the listing  (the query returns group membership info ), the only 
thing being shown in the question listing is the member name ... so perhaps 
this is also a case of eager loading :

SELECT parliament_memberships.region_id AS parliament_memberships_region_id, 
user_group_memberships.end_date AS end_date, parliament_memberships.party_id AS 
parliament_memberships_party_id, parliament_memberships.province_id AS 
parliament_memberships_province_id, parliament_memberships.constituency_id AS 
parliament_memberships_constituency_id, user_group_memberships.start_date AS 
start_date, user_group_memberships.membership_id AS 
user_group_memberships_membership_id, parliament_memberships.membership_id AS 
parliament_memberships_membership_id, user_group_memberships.user_id AS 
user_group_memberships_user_id, user_group_memberships.group_id AS 
user_group_memberships_group_id, user_group_memberships.notes AS 
user_group_memberships_notes, user_group_memberships.active_p AS 
user_group_memberships_active_p, user_group_memberships.replaced_id AS 
user_group_memberships_replaced_id, user_group_memberships.substitution_type AS 
user_group_memberships_substitution_type, 
user_group_memberships.membership_type AS 
user_group_memberships_membership_type, user_group_memberships.language AS 
user_group_memberships_language, parliament_memberships.member_election_type_id 
AS parliament_memberships_member_election_type_id, 
parliament_memberships.election_nomination_date AS 
parliament_memberships_election_nomination_date, 
parliament_memberships.leave_reason AS parliament_memberships_leave_reason, 
provinces_1.province_id AS provinces_1_province_id, provinces_1.province AS 
provinces_1_province, provinces_1.language AS provinces_1_language, 
regions_1.region_id AS regions_1_region_id, regions_1.region AS 
regions_1_region, regions_1.language AS regions_1_language, E'group.' || 
anon_2.groups_type || E'.' || CAST(anon_2.groups_group_id AS VARCHAR) AS 
anon_1, anon_2.groups_group_id AS anon_2_groups_group_id, 
anon_2.groups_short_name AS anon_2_groups_short_name, anon_2.groups_full_name 
AS anon_2_groups_full_name, anon_2.groups_description AS 
anon_2_groups_description, anon_2.groups_status AS anon_2_groups_status, 
anon_2.groups_status_date AS anon_2_groups_status_date, 
anon_2.groups_start_date AS anon_2_groups_start_date, anon_2.groups_end_date AS 
anon_2_groups_end_date, anon_2.groups_type AS anon_2_groups_type, 
anon_2.groups_parent_group_id AS anon_2_groups_parent_group_id, 
anon_2.groups_language AS anon_2_groups_language, anon_2.groups_custom1 AS 
anon_2_groups_custom1, anon_2.groups_custom2 AS anon_2_groups_custom2, 
anon_2.groups_custom3 AS anon_2_groups_custom3, anon_2.groups_custom4 AS 
anon_2_groups_custom4, anon_2.political_parties_party_id AS 
anon_2_political_parties_party_id, anon_2.political_parties_logo_data AS 
anon_2_political_parties_logo_data, anon_2.political_parties_logo_name AS 
anon_2_political_parties_logo_name, anon_2.political_parties_logo_mimetype AS 
anon_2_political_parties_logo_mimetype, constituencies_1.constituency_id AS 
constituencies_1_constituency_id, constituencies_1.name AS 
constituencies_1_name, constituencies_1.start_date AS 
constituencies_1_start_date, constituencies_1.end_date AS 
constituencies_1_end_date, constituencies_1.language AS 
constituencies_1_language, member_election_types_1.member_election_type_id AS 
member_election_types_1_member_election_type_id, 
member_election_types_1.member_election_type_name AS 
member_election_types_1_member_election_type_name, 
member_election_types_1.language AS member_election_types_1_language, 
users_1.user_id AS users_1_user_id, users_1.login AS users_1_login, 
users_1.titles AS users_1_titles, users_1.first_name AS users_1_first_name, 
users_1.last_name AS users_1_last_name, users_1.middle_name AS 
users_1_middle_name, users_1.email AS users_1_email, users_1.gender AS 
users_1_gender, users_1.date_of_birth AS users_1_date_of_birth, 
users_1.birth_country AS users_1_birth_country, users_1.birth_nationality AS 
users_1_birth_nationality, users_1.current_nationality AS 
users_1_current_nationality, users_1.uri AS users_1_uri, users_1.date_of_death 
AS users_1_date_of_death, users_1.type_of_id AS users_1_type_of_id, 
users_1.national_id AS users_1_national_id, users_1.password AS 
users_1_password, users_1.salt AS users_1_salt, users_1.description AS 
users_1_description, users_1.image AS users_1_image, users_1.active_p AS 
users_1_active_p, users_1.receive_notification AS users_1_receive_notification, 
users_1.language AS users_1_language
    FROM user_group_memberships JOIN parliament_memberships ON user_group_memberships.membership_id = parliament_memberships.membership_id LEFT OUTER JOIN provinces AS provinces_1 ON parliament_memberships.province_id = provinces_1.province_id LEFT OUTER JOIN regions AS regions_1 ON parliament_memberships.region_id = regions_1.region_id LEFT OUTER JOIN (SELECT groups.group_id AS groups_group_id, groups.short_name AS groups_short_name, groups.full_name AS groups_full_name, groups.description AS groups_description, groups.status AS groups_status, groups.status_date AS groups_status_date, groups.start_date AS groups_start_date, groups.end_date AS groups_end_date, groups.type AS groups_type, groups.parent_group_id AS groups_parent_group_id, groups.language AS groups_language, groups.custom1 AS groups_custom1, groups.custom2 AS groups_custom2, groups.custom3 AS groups_custom3, groups.custom4 AS groups_custom4, political_parties.party_id AS political_parties_party_id, political_parties.logo_data AS political_parties_logo_data, political_parties.logo_name AS political_parties_logo_name, political_parties.logo_mimetype AS political_parties_logo_mimetype
    FROM groups JOIN political_parties ON groups.group_id = political_parties.party_id) AS anon_2 ON parliament_memberships.party_id = anon_2.political_parties_party_id AND anon_2.groups_type IN (E'political-party') LEFT OUTER JOIN constituencies AS constituencies_1 ON parliament_memberships.constituency_id = constituencies_1.constituency_id LEFT OUTER JOIN member_election_types AS member_election_types_1 ON member_election_types_1.member_election_type_id = parliament_memberships.member_election_type_id LEFT OUTER JOIN users AS users_1 ON user_group_memberships.user_id = users_1.user_id
    WHERE user_group_memberships.user_id = 4

Original comment by ashok.ha...@gmail.com on 2 Sep 2011 at 7:17

[GoogleCodeExporter]

Currently content translations are looked up in the translations table by 
looking up the translations by object -- for e.g. for every item in a listing, 
the translations are looked up in this way :

SELECT translations.object_id AS translations_object_id, 
translations.object_type AS translations_object_type, translations.lang AS 
translations_lang, translations.field_name AS translations_field_name, 
translations.field_text AS translations_field_text
    FROM translations
    WHERE translations.object_type = E'Question' AND translations.object_id = 3089 AND translations.lang = E'en_US'

However -- content translations are really an edge-case (i.e. there are more 
uni-lingual parliaments than bi-lingual ones ) -- so in most cases the 
translations table is going to be empty (as is the case above). So why lookup 
the content translations at all ?

I would propose an environment parameter which specifies whether "content 
translations" are enabled or not. If its not being used - we simply switch it 
off at setup time -- and all these unneccessary requests can be avoided.

Original comment by ashok.ha...@gmail.com on 2 Sep 2011 at 7:34

[GoogleCodeExporter]

Item loading -- querying attachemnts -- 

SELECT attached_files.attached_file_id AS attached_files_attached_file_id, 
attached_files.item_id AS attached_files_item_id, 
attached_files.attached_file_type_id AS attached_files_attached_file_type_id, 
attached_files.file_version_id AS attached_files_file_version_id, 
attached_files.file_title AS attached_files_file_title, 
attached_files.file_description AS attached_files_file_description, 
attached_files.file_data AS attached_files_file_data, attached_files.file_name 
AS attached_files_file_name, attached_files.file_mimetype AS 
attached_files_file_mimetype, attached_files.status AS attached_files_status, 
attached_files.status_date AS attached_files_status_date, 
attached_files.language AS attached_files_language 
    FROM attached_files 
    WHERE 3112 = attached_files.item_id

Analysis -- 

"Seq Scan on attached_files  (cost=0.00..65.80 rows=1 width=147) (actual 
time=0.836..0.836 rows=0 loops=1)"
"  Filter: (3112 = item_id)"
"Total runtime: 0.881 ms"

Needs an index on item_id 

CREATE INDEX att_itemid_idx
  ON attached_files
  USING btree
  (item_id);

After index :

"Index Scan using att_itemid_idx on attached_files  (cost=0.00..8.27 rows=1 
width=147) (actual time=0.018..0.018 rows=0 loops=1)"
"  Index Cond: (3112 = item_id)"
"Total runtime: 0.069 ms"

more than 10 times faster

Original comment by ashok.ha...@gmail.com on 2 Sep 2011 at 9:13

[GoogleCodeExporter]

group_assignments , needs an index on item_id,

SELECT count(1) AS count_1 
    FROM group_assignments 
    WHERE group_assignments.item_id = 3112

Index : 
CREATE INDEX grpassign_itemid_idx
  ON group_assignments
  USING btree
  (item_id);

Original comment by ashok.ha...@gmail.com on 2 Sep 2011 at 9:16

[GoogleCodeExporter]

Index on settings table :

CREATE INDEX sett_propertysheet_idx
  ON settings
  USING btree
  (propertysheet);

Original comment by ashok.ha...@gmail.com on 2 Sep 2011 at 9:25

[GoogleCodeExporter]

With r8579, queries such as in Comment #10 , were simplified to include the new 
group_principal_id field  :

SELECT parliamentary_items.parliamentary_item_id AS 
parliamentary_items_parliamentary_item_id, parliamentary_items.parliament_id AS 
parliamentary_items_parliament_id, parliamentary_items.owner_id AS 
parliamentary_items_owner_id, parliamentary_items.language AS 
parliamentary_items_language, parliamentary_items.short_name AS 
parliamentary_items_short_name, parliamentary_items.full_name AS 
parliamentary_items_full_name, parliamentary_items.body_text AS 
parliamentary_items_body_text, parliamentary_items.description AS 
parliamentary_items_description, parliamentary_items.subject AS 
parliamentary_items_subject, parliamentary_items.coverage AS 
parliamentary_items_coverage, parliamentary_items.status AS 
parliamentary_items_status, parliamentary_items.status_date AS 
parliamentary_items_status_date, parliamentary_items.registry_number AS 
parliamentary_items_registry_number, parliamentary_items.uri AS 
parliamentary_items_uri, parliamentary_items.note AS parliamentary_items_note, 
parliamentary_items.receive_notification AS 
parliamentary_items_receive_notification, parliamentary_items.type AS 
parliamentary_items_type, parliamentary_items.geolocation AS 
parliamentary_items_geolocation, parliamentary_items.custom1 AS 
parliamentary_items_custom1, parliamentary_items.custom2 AS 
parliamentary_items_custom2, parliamentary_items.custom3 AS 
parliamentary_items_custom3, parliamentary_items.custom4 AS 
parliamentary_items_custom4, parliamentary_items.timestamp AS 
parliamentary_items_timestamp, questions.question_id AS questions_question_id, 
questions.question_number AS questions_question_number, 
questions.ministry_submit_date AS questions_ministry_submit_date, 
questions.question_type_id AS questions_question_type_id, 
questions.response_type_id AS questions_response_type_id, 
questions.supplement_parent_id AS questions_supplement_parent_id, 
questions.sitting_time AS questions_sitting_time, questions.ministry_id AS 
questions_ministry_id, questions.response_text AS questions_response_text, 
response_types_1.response_type_id AS response_types_1_response_type_id, 
response_types_1.response_type_name AS response_types_1_response_type_name, 
response_types_1.language AS response_types_1_language, 
question_types_1.question_type_id AS question_types_1_question_type_id, 
question_types_1.question_type_name AS question_types_1_question_type_name, 
question_types_1.language AS question_types_1_language, groups_1.group_id AS 
groups_1_group_id, groups_1.short_name AS groups_1_short_name, 
groups_1.full_name AS groups_1_full_name, groups_1.description AS 
groups_1_description, groups_1.status AS groups_1_status, groups_1.status_date 
AS groups_1_status_date, groups_1.start_date AS groups_1_start_date, 
groups_1.end_date AS groups_1_end_date, groups_1.type AS groups_1_type, 
groups_1.group_principal_id AS groups_1_group_principal_id, 
groups_1.parent_group_id AS groups_1_parent_group_id, groups_1.language AS 
groups_1_language, groups_1.custom1 AS groups_1_custom1, groups_1.custom2 AS 
groups_1_custom2, groups_1.custom3 AS groups_1_custom3, groups_1.custom4 AS 
groups_1_custom4, users_1.user_id AS users_1_user_id, users_1.login AS 
users_1_login, users_1.titles AS users_1_titles, users_1.first_name AS 
users_1_first_name, users_1.last_name AS users_1_last_name, users_1.middle_name 
AS users_1_middle_name, users_1.email AS users_1_email, users_1.gender AS 
users_1_gender, users_1.date_of_birth AS users_1_date_of_birth, 
users_1.birth_country AS users_1_birth_country, users_1.birth_nationality AS 
users_1_birth_nationality, users_1.current_nationality AS 
users_1_current_nationality, users_1.uri AS users_1_uri, users_1.date_of_death 
AS users_1_date_of_death, users_1.type_of_id AS users_1_type_of_id, 
users_1.national_id AS users_1_national_id, users_1.password AS 
users_1_password, users_1.salt AS users_1_salt, users_1.description AS 
users_1_description, users_1.image AS users_1_image, users_1.active_p AS 
users_1_active_p, users_1.receive_notification AS users_1_receive_notification, 
users_1.language AS users_1_language 
    FROM parliamentary_items JOIN questions ON parliamentary_items.parliamentary_item_id = questions.question_id LEFT OUTER JOIN response_types AS response_types_1 ON response_types_1.response_type_id = questions.response_type_id LEFT OUTER JOIN question_types AS question_types_1 ON question_types_1.question_type_id = questions.question_type_id LEFT OUTER JOIN groups AS groups_1 ON groups_1.group_id = questions.ministry_id AND groups_1.type IN (E'ministry') LEFT OUTER JOIN users AS users_1 ON parliamentary_items.owner_id = users_1.user_id 
    WHERE parliamentary_items.status IN (E'submitted', E'received', E'admissible', E'response_submitted')

About 5 miliseconds faster :

"Hash Left Join  (cost=49.12..1553.66 rows=1053 width=7998) (actual 
time=0.110..9.613 rows=1047 loops=1)"
"  Hash Cond: (questions.response_type_id = response_types_1.response_type_id)"
"  ->  Hash Left Join  (cost=35.97..1526.03 rows=1053 width=7454) (actual 
time=0.096..9.113 rows=1047 loops=1)"
"        Hash Cond: (questions.question_type_id = 
question_types_1.question_type_id)"
"        ->  Hash Left Join  (cost=22.82..1498.40 rows=1053 width=6910) (actual 
time=0.085..8.559 rows=1047 loops=1)"
"              Hash Cond: (questions.ministry_id = groups_1.group_id)"
"              ->  Hash Left Join  (cost=11.93..1483.41 rows=1053 width=5826) 
(actual time=0.064..7.953 rows=1047 loops=1)"
"                    Hash Cond: (parliamentary_items.owner_id = 
users_1.user_id)"
"                    ->  Merge Join  (cost=1.48..1458.49 rows=1053 width=2153) 
(actual time=0.039..7.158 rows=1047 loops=1)"
"                          Merge Cond: 
(parliamentary_items.parliamentary_item_id = questions.question_id)"
"                          ->  Index Scan using parliamentary_items_pkey on 
parliamentary_items  (cost=0.00..778.36 rows=1054 width=1184) (actual 
time=0.023..4.005 rows=1047 loops=1)"
"                                Filter: ((status)::text = ANY 
('{submitted,received,admissible,response_submitted}'::text[]))"
"                          ->  Index Scan using questions_pkey on questions  
(cost=0.00..656.33 rows=4491 width=969) (actual time=0.010..2.169 rows=3107 
loops=1)"
"                    ->  Hash  (cost=10.20..10.20 rows=20 width=3673) (actual 
time=0.014..0.014 rows=9 loops=1)"
"                          ->  Seq Scan on users users_1  (cost=0.00..10.20 
rows=20 width=3673) (actual time=0.005..0.006 rows=9 loops=1)"
"              ->  Hash  (cost=10.88..10.88 rows=1 width=1084) (actual 
time=0.013..0.013 rows=1 loops=1)"
"                    ->  Seq Scan on groups groups_1  (cost=0.00..10.88 rows=1 
width=1084) (actual time=0.010..0.011 rows=1 loops=1)"
"                          Filter: ((type)::text = 'ministry'::text)"
"        ->  Hash  (cost=11.40..11.40 rows=140 width=544) (actual 
time=0.005..0.005 rows=2 loops=1)"
"              ->  Seq Scan on question_types question_types_1  
(cost=0.00..11.40 rows=140 width=544) (actual time=0.003..0.004 rows=2 loops=1)"
"  ->  Hash  (cost=11.40..11.40 rows=140 width=544) (actual time=0.004..0.004 
rows=2 loops=1)"
"        ->  Seq Scan on response_types response_types_1  (cost=0.00..11.40 
rows=140 width=544) (actual time=0.002..0.002 rows=2 loops=1)"
"Total runtime: 9.906 ms"

Original comment by ashok.ha...@gmail.com on 5 Sep 2011 at 3:15

[GoogleCodeExporter]

Closing All these issues -- As the list is being updated to match with the 
whiteboard backlog.

Original comment by ashok.ha...@gmail.com on 7 Sep 2012 at 7:57

• Changed state: Closed