Hm. Tried running ANALYZE. Took almost 10 minutes to run. (Don't know if it
would have been run automatically since I last tried this yesterday, but
figured it couldn't hurt.) Still, no difference:
http://explain.depesz.com/s/xHq
Actually, it's 10x worse (maybe because this is my first time
Here's my query:
SELECT foursquare.name, foursquare.city, COUNT(moment_id) AS popularity
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 =
Alessandro Gagliardi alessan...@path.com writes:
WHERE ... (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
I tried: CREATE INDEX blocks_created_at_timezone_idx ON blocks USING btree
((created at time zone timezone));
(Actually, I originally did try one on (created at time zone
timezone)::date but couldn't figure out how to phrase it in a way
PostgreSQL would accept.)
Anyway, no difference:
Alessandro Gagliardi alessan...@path.com wrote:
(Actually, I originally did try one on (created at time zone
timezone)::date but couldn't figure out how to phrase it in a way
PostgreSQL would accept.)
CREATE INDEX blocks_created_date_idx
ON blocks
USING btree
(((created at time zone
Still slow as mud: http://explain.depesz.com/s/Zfn
Now I've got indices on created, timezone, created at time zone timezone,
and (created at time zone timezone)::date. Clearly the problem isn't a lack
of indices!...except, wait, it's not actually using blocks_created_date_idx
(or
Alessandro Gagliardi alessan...@path.com writes:
Still slow as mud: http://explain.depesz.com/s/Zfn
Now I've got indices on created, timezone, created at time zone timezone,
and (created at time zone timezone)::date. Clearly the problem isn't a lack
of indices!...except, wait, it's not
