Re: Advise needed for a join query with a where conditional

On Thu, Dec 10, 2015 at 8:38 PM, ankur_adwyze <ankur(at)adwyze(dot)com> wrote:
> Hi Folks,
>
> I am a newbie to this mailing list. Tried searching the forum but didn't
> find something similar to the problem I am facing.
>
> Background:
> I have a Rails app with Postgres db. For certain reports, I have to join
> multiple tables. However, certain join queries are dog slow and I am
> wondering if I am missing any index.

Are you vacuuming and analyzing your database appropriately? What
non-default config settings do you have.

Something certainly seems suspicious about custom_tags_fb_ad_groups
and its index.

-> Index Only Scan using custom_tags_fb_ad_groups_index on
custom_tags_fb_ad_groups custom_tags_fb_ad_groups_1
(cost=0.42..1728.30 rows=1 width=8) (actual time=1.352..3.815 rows=1
loops=32934)
Index Cond: (fb_ad_group_id = fb_ad_group_reports.fb_ad_group_id)
Heap Fetches: 32934

Doing a single-value look up into an index should have an estimated
cost of around 9, unless you did something screwy with your cost_*
parameter settings. Why does it think it is 1728.30 instead? Is the
index insanely bloated? And it actually is slow to do those look ups,
which is where almost all of your time is going.

And, why isn't it just using a hash join on that table, since you are
reading so much of it?

I'd do a VACUUM FULL of that table, then a regular VACUUM on it (or
the entire database), then ANALYZE it (or your entire database), and
see if that took care of the problem.