Optimize IE queries

[AmyKort]

@AmyKort commented on Tue Dec 05 2017

The filters on the independent expenditure search appear not to be working. We've concluded that this behavior is a result of a performance issue, where a 500 error being thrown almost immediately for one of the underlying API calls:

https://api.open.fec.gov/v1/schedules/schedule_e/?sort_hide_null=true&data_type=processed&committee_id=C30001911&committee_id=C90013244&cycle=2014&is_notice=false&sort=-expenditure_date&per_page=30
And as described, if you look at the page, the 2013 - 2014 choice in the Years filter is behaving as if it is still waiting for a response.

The ability to search and filter IEs is an important tool for users wanting to better understand campaign spending, so fixing this bug is a top priority.

Original identification of the problem:

@rshorey commented on Thu Nov 30 2017

I added a bunch of filters to the independent expenditure search, but it appears to be showing me everything. I tried on multiple browsers (including one that won't have any cached visits to FEC sites)

Here's the URL:
https://www.fec.gov/data/independent-expenditures/?data_type=processed&committee_id=C30001911&committee_id=C90013244&cycle=2014&is_notice=false

here's a screenshot of it showing my filters and then showing a bunch of unrelated results:

Thanks!

---

@LindsayYoung commented on Thu Nov 30 2017

thanks for reporting @rshorey!

---

@jwchumley commented on Wed Dec 13 2017

I think this is a performance issue @LindsayYoung @ccostino. If you click on the link:
https://www.fec.gov/data/independent-expenditures/?data_type=processed&committee_id=C30001911&committee_id=C90013244&cycle=2014&is_notice=false

It seems to show everything but I think it's hanging up because the 2013-2014 filter is still cycling like it isn't done.

If you deselect the two committees and the cycle then try selecting them again you can't select them all without a message saying "We had trouble processing your request
Please try again. If you still have trouble, let us know"

---

@ccostino commented on Fri Dec 15 2017

@LindsayYoung, now that I think about this more I have a sneaking suspicion given the behavior that we might be missing an index(es) on one of the columns or we need a multicolumn index(es) similar to what we had to do with schedule A and B to account for the sorting and paging.

---

@ccostino commented on Fri Dec 15 2017

Here are the current indexes on ofec_sched_e for reference:

Indexes:
    "ofec_sched_e_sub_id_pkey" PRIMARY KEY, btree (sub_id)
    "idx_ofec_sched_e_cal_ytd_ofc_sought_sub_id" btree (cal_ytd_ofc_sought, sub_id)
    "idx_ofec_sched_e_cmte_id" btree (cmte_id)
    "idx_ofec_sched_e_cycle_rpt_yr" btree (get_cycle(rpt_yr))
    "idx_ofec_sched_e_entity_tp" btree (entity_tp)
    "idx_ofec_sched_e_exp_amt_sub_id" btree (exp_amt, sub_id)
    "idx_ofec_sched_e_exp_dt_sub_id" btree (exp_dt, sub_id)
    "idx_ofec_sched_e_filing_form" btree (filing_form)
    "idx_ofec_sched_e_image_num" btree (image_num)
    "idx_ofec_sched_e_is_notice" btree (is_notice)
    "idx_ofec_sched_e_payee_name_text" gin (payee_name_text)
    "idx_ofec_sched_e_pg_date" btree (pg_date)
    "idx_ofec_sched_e_rpt_yr" btree (rpt_yr)
    "idx_ofec_sched_e_s_o_cand_id" btree (s_o_cand_id)

---

@PaulClark2 commented on Sun Feb 04 2018

What's the status of this issue?

---

@ccostino commented on Mon Feb 05 2018

I just took a look and this is still an open issue and will need to be investigated further.

There's a 500 error being thrown almost immediately for one of the underlying API calls:

https://api.open.fec.gov/v1/schedules/schedule_e/?sort_hide_null=true&data_type=processed&committee_id=C30001911&committee_id=C90013244&cycle=2014&is_notice=false&sort=-expenditure_date&per_page=30

And as described, if you look at the page, the 2013 - 2014 choice in the Years filter is behaving as if it is still waiting for a response.