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
On Thu, Feb 9, 2012 at 3:41 PM, Peter van Hardenberg wrote:
> Hmm, perhaps we could usefully aggregate auto_explain output.
How about something where you run a site at random_page cost of x,
then y, then z and you do some aggregating of query times in each. A
scatter plot should tell you lots.
Hmm, perhaps we could usefully aggregate auto_explain output.
On Thu, Feb 9, 2012 at 7:32 AM, Jeff Janes wrote:
> On Wed, Feb 8, 2012 at 6:28 PM, Scott Marlowe wrote:
>> On Wed, Feb 8, 2012 at 6:45 PM, Peter van Hardenberg wrote:
>>> Having read the thread, I don't really see how I could study
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
On Wed, Feb 8, 2012 at 6:28 PM, Scott Marlowe wrote:
> On Wed, Feb 8, 2012 at 6:45 PM, Peter van Hardenberg wrote:
>> Having read the thread, I don't really see how I could study what a
>> more principled value would be.
>
> Agreed. Just pointing out more research needs to be done.
>
>> That sai
Am 07.02.2012 18:40, schrieb Ofer Israeli:
> Table 1:
> 46 columns
> 23 indexes on fields of the following types:
> INTEGER - 7
> TIMESTAMP - 2
> VARCHAR - 12
> UUID - 2
>
> 23 columns
> 12 indexes on fields of the following types:
> INTEGER - 3
> TIMESTAMP - 1
> VARCHAR - 6
> UUID - 2
Are you re
On Wed, Feb 8, 2012 at 20:22, Ofer Israeli wrote:
> Andy Colson wrote:
> > Oh, I knew I'd seen index usage stats someplace.
> >
> > give this a run:
> >
> > select * from pg_stat_user_indexes where relname = 'SuperBigTable';
> >
> > http://www.postgresql.org/docs/current/static/monitoring-stats.h
11 matches
Mail list logo
