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 running
Alessandro Gagliardi 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 actually using blocks_c
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 blocks_created_at
Alessandro Gagliardi 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 timezone)::date)
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: http://ex
Alessandro Gagliardi 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 there is a particula
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 = 'U
