Here's my query:
FROM foursq_categories
JOIN foursquare USING (foursq_id)
JOIN places USING (foursq_id)
JOIN blocks USING (block_id)
WHERE "primary"
AND (created at time zone timezone)::date = 'yesterday'
AND (country = 'USA' OR country = 'United States')
GROUP BY foursq_id,
foursquare.name, foursquare.city ORDER BY popularity DESC LIMIT 12;
To my surprise, it was not the tsquery that made this slow (which is awesome, because I was worried about that) but rather the filter: (created at time zone timezone)::date = 'yesterday'
created has an index (btree if it matters). timezone does not. I'm wondering if the solution to my problem is to create a joint index between created and timezone (and if so, if there is a particular way to do that to make it work the way I want).
Thanks in advance.
-Alessandro
