| From: | Alessandro Gagliardi <alessandro(at)path(dot)com> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Subject: | timestamp with time zone |
| Date: | 2012-02-09 18:42:15 |
| Message-ID: | CAAB3BBJnD+dcaOQ_T8g+y==zen9yuOj2NvXrE5vhVe=RP28hMw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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 = 'USA' OR country = 'United States')
AND foursq_categories.name @@ to_tsquery('Restaurant')
GROUP BY foursq_id, foursquare.name, foursquare.city ORDER BY popularity
DESC LIMIT 12;
Here's my explain: http://explain.depesz.com/s/xoH
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2012-02-09 19:46:06 | Re: timestamp with time zone |
| Previous Message | Jeff Janes | 2012-02-09 15:32:19 | Re: random_page_cost = 2.0 on Heroku Postgres |