On Oct 7, 2014, at 10:02 AM, Marc Mamin wrote:
> Hi,
> it seems to me that your subquery may deliver duplicate ids.
> And with the selectivity of your example, I would expect an index usage
> instead of a table scan. You may check how up to date your statistics are
> and try to raise the statistic target on the column resource_2_tag.tag_id.
> Also try a CTE form for your query:
It shouldn't be able to deliver duplicate ids.
=> SELECT COUNT(*) FROM (SELECT DISTINCT id FROM resource WHERE resource_attribute1_id = 614) AS foo;
count ------- 5184
=> SELECT COUNT(*) FROM (SELECT id FROM resource WHERE resource_attribute1_id = 614) AS foo;
count ------- 5184
However, adding in the DISTINCT drastically changed the query plan, and did give a speedup.
Your comment made me focus on the notion of a Table Scan. I assumed it did the seq scan - and there would not be much
savingsotherwise - because the table is just 2 ids.
I was wrong.
I noticed that I never put a PRIMARY KEY constraint on that table.
So i tried adding a PRIMARY KEY constraint, then running vacuum analyze...
And that solved all my problems.
the original query ended up being the fastest at 260ms ( down from 1760 )
Join - 260ms
Subquery w/DISTINCT - 300ms
CTE - 330
CTE w/DISTINCT - 345ms
Subquery (no DISTINCT) - 1500ms