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 savings otherwise - 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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to