Skip site navigation (1) Skip section navigation (2)

timestamp with time zone

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: (view raw or whole thread)
Lists: pgsql-performance
Here's my query:

SELECT,, 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 @@ to_tsquery('Restaurant')
GROUP BY foursq_id,, ORDER BY popularity

Here's my explain:

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.



pgsql-performance by date

Next:From: Tom LaneDate: 2012-02-09 19:46:06
Subject: Re: timestamp with time zone
Previous:From: Jeff JanesDate: 2012-02-09 15:32:19
Subject: Re: random_page_cost = 2.0 on Heroku Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group