On Thu, Feb 2, 2012 at 6:52 AM, Merlin Moncure wrote:
> also, is effective_cache_size set to a reasonable value?
>
> Yeah, it's 153kB
On Wed, Feb 1, 2012 at 12:48 PM, Alessandro Gagliardi
wrote:
> LIMIT 65536; Total query runtime: 14846 ms.
> - http://explain.depesz.com/s/I3E
> LIMIT 69632: Total query runtime: 80141 ms.
> - http://explain.depesz.com/s/9hp
>
> So it looks like when the limit crosses a certain threshold (somewher
On Wed, Feb 1, 2012 at 11:04 AM, Bob Lunney wrote:
> Possibly. What does
>
> psql > show work_mem;
>
> say?
>
> 100MB
Possibly. What does
psql > show work_mem;
say?
Bob Lunney
From: Alessandro Gagliardi
To: pgsql-performance@postgresql.org
Sent: Wednesday, February 1, 2012 12:19 PM
Subject: Re: [PERFORM] From Simple to Complex
Final update on this thread: since it
On Wed, Feb 1, 2012 at 11:48 AM, Alessandro Gagliardi
wrote:
> LIMIT 65536; Total query runtime: 14846 ms.
> - http://explain.depesz.com/s/I3E
> LIMIT 69632: Total query runtime: 80141 ms.
> - http://explain.depesz.com/s/9hp
>
> So it looks like when the limit crosses a certain threshold (somewher
LIMIT 65536; Total query runtime: 14846 ms. -
http://explain.depesz.com/s/I3E
LIMIT 69632: Total query runtime: 80141 ms. -
http://explain.depesz.com/s/9hp
So it looks like when the limit crosses a certain threshold (somewhere
north of 2^16), Postgres decides to do a Seq Scan instead of an Index S
On Wed, Feb 1, 2012 at 11:19 AM, Alessandro Gagliardi
wrote:
> Final update on this thread: since it is only necessary for me to get a
> rough ratio of the distribution (and not the absolute count), I refactored
> the query to include a subquery that samples from the moments table
> thus: SELECT m
Final update on this thread: since it is only necessary for me to get a
rough ratio of the distribution (and not the absolute count), I refactored
the query to include a subquery that samples from the moments table
thus: SELECT moment_id, block_id FROM moments WHERE inserted BETWEEN
'yesterday' AND
I just got a pointer on presenting EXPLAIN ANALYZE in a more human friendly
fashion (thanks, Agent M!): http://explain.depesz.com/s/A9S
>From this it looks like the bottleneck happens when Postgres does an Index
Scan using emotions_moment_id_idx on emotions before filtering on
moments.inserted so
I changed the query a bit so the results would not change over the
course of the day to:
SELECT relname, emotion, COUNT(feedback_id) FROM pg_class, moments
JOIN emotions USING (moment_id)
WHERE moments.inserted BETWEEN 'yesterday' AND 'today' AND
moments.tableoid = pg_class.oid
GROUP BY relname, e
Looks like I missed a key sentence in
http://www.postgresql.org/docs/9.0/static/ddl-inherit.html which states: "A
serious limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single
tables, not to their inheritance children."
My slow query today is somewhat more complex than yesterday's, but I'm
hopeful it can be improved. Here's the query:
SELECT relname, emotion, COUNT(feedback_id) FROM pg_class, moments
JOIN emotions USING (moment_id)
WHERE moments.inserted > 'today' AND moments.tableoid = pg_class.oid
GROUP BY reln
12 matches
Mail list logo
