How I managed to beat the Postgres optimizer…

Remember my post about 3 weeks ago where I told you guys, how I optimized “everything” for about 1,500 times? Well, apparently, it was not enough :).

Specifically, I’ve managed to optimized several reports, which ran often, each taking up to 11 minutes(!) to about 60 milliseconds. However, there were several other reports, which also ran quite often, and ran… well, for about 1.5 – 2.5 sec. And I knew, they could run faster!

So as a first step I’ve decided to create a function, which as a first step would pre-select the id for a specific report type and report field. This would allow me to avoid two joins in the final SELECT, which when generated, looked like this:


select distinct person_id from
reports r
join report_values rv on r.report_id=rv.report_id and report_type_id=9
and rv.report_field_id=250
and rv.value in
(select rv1.value from reports cr1
join report_values rv1 on rv1.report_id=r1.report_id and
rv1.value '' and r1.report_type_id=9
and rv1.report_field_id=250
and person_id=p_person_id)

As you can see, the goal is to find ALL people with the specific report value matching the one for a person in question. Here I’ve already pre-selected the report type and report field, so those IDs were included into the generated SQL.

I’ve expected this to run not more than 100 ms, since all primary-foreign key, indexes, etc were on place. However, no matter how hard I’ve tried, I waa getting the execution time about 1 sec. Why????

That’s why: the execution plan was showing… a HASH JOIN with the second copy of report_values table (rv), no matter how hard I’ve tried! For some reason the Postgres optimizer waz “forgetting” about PK-FK relation here!

I’ve tried to run a query with the set of values explicitly listed in the parenthesis, and it got executed correctly, using the index. The list itself was also selected using all the right indexes. So how come, the two selects separately were executing in about 15-20 ms each, and the whole thing – for 1000 ms?!

After spending two days trying to convince the optimizer, I gave in and had to act against the things, which I preach – I had to explain the optimizer, how to do it!

Here is what I did: inside the function I first selected the list of values:


FOR v_interim_rec IN (select rv1.value from reports r1
join report_values rv1 on rv1.report_id=r1.report_id and
rv1.value '' and r1.report_type_id=9
and rv1.report_field_id=250 and person_id=p_person_id)

Then I concatenated the results in the separate text variable:

LOOP
IF length(v_values_list)>1 THEN
v_values_list:=v_values_list||',';
END IF;
v_values_list:=v_values_list||$$'$$||v_interim_rec.value||$$'$$;
END LOOP;

And then I used this string to generate a final select:


v_select_final :=$$select distinct person_id from
reports r
join report_values rv on r.report_id=rv.report_id and report_type_id=9
and rv.report_field_id=250 and rv.value in $$||v_values_list;

Guess, how long does it take now to run? 20 milliseconds! And more importantly, this solution is very scalable, it will continue to perform, even if/when the number of people and/or reports will significantly increase.

But why the optimizer couldn’t do this???

Advertisements

1 Comment

Filed under Data management, SQL

One response to “How I managed to beat the Postgres optimizer…

  1. The optimizer doesn’t take foreign keys into account (except for a very limited case of table elimination). Planning decisions are made based strictly on column statistics.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s