shared_buffers is big enough to hold the entire database, and there is plenty
of extra space. (verified with PG_buffercache)
So i don't think that is the reason.
Tom Lane schrieb:
>Jeff Janes writes:
>> On 7/12/11, lars wrote:
>>> The fact that a select (maybe a big analytical query we'll
On 07/13/2011 02:53 AM, Mario Splivalo wrote:
On 07/13/2011 12:39 AM, Tom Lane wrote:
Mario Splivalo writes:
On 07/12/2011 10:04 PM, Tom Lane wrote:
What you need to look into is why the estimated join size is 9400 rows
when the actual join size is zero. Are both tables ANALYZEd? Are you
inten
Jeff Janes writes:
> On 7/12/11, lars wrote:
>> The fact that a select (maybe a big analytical query we'll run) touching
>> many rows will update the WAL and wait
>> (apparently) for that IO to complete is making a fully cached database
>> far less useful.
>> I just artificially created this scen
On 07/13/2011 12:39 AM, Tom Lane wrote:
Mario Splivalo writes:
On 07/12/2011 10:04 PM, Tom Lane wrote:
What you need to look into is why the estimated join size is 9400 rows
when the actual join size is zero. Are both tables ANALYZEd? Are you
intentionally selecting rows that have no join pa
On 7/12/11, lars wrote:
>
>
> The fact that a select (maybe a big analytical query we'll run) touching
> many rows will update the WAL and wait
> (apparently) for that IO to complete is making a fully cached database
> far less useful.
> I just artificially created this scenario.
I can't think of
On 07/13/2011 12:39 AM, Tom Lane wrote:
Mario Splivalo writes:
On 07/12/2011 10:04 PM, Tom Lane wrote:
What you need to look into is why the estimated join size is 9400 rows
when the actual join size is zero. Are both tables ANALYZEd? Are you
intentionally selecting rows that have no join pa
On 07/12/2011 02:38 PM, Merlin Moncure wrote:
Something is not adding up here. Perhaps there is an alternate route
to WAL logged activity from selects I'm not thinking of. Right now
I'm thinking to run the selects on table 'a' and the inserts
concurrently on table 'b' and seeing how that behav
Mario Splivalo writes:
> On 07/12/2011 10:04 PM, Tom Lane wrote:
>> What you need to look into is why the estimated join size is 9400 rows
>> when the actual join size is zero. Are both tables ANALYZEd? Are you
>> intentionally selecting rows that have no join partners?
> Yes, both tables have
On 07/12/2011 02:51 PM, Kevin Grittner wrote:
I ran x a bunch of times to get a baseline, then y once, then x a
bunch more times. The results were a bit surprising:
cir=> \timing
Timing is on.
cir=> execute x('001','2011-6-30');
count
---
3456
(1 row)
Time: 9.823 ms
cir=
On 07/12/2011 10:04 PM, Tom Lane wrote:
Mario Splivalo writes:
Limit (cost=0.00..415.91 rows=21 width=8) (actual
time=11263.089..11263.089 rows=0 loops=1)
-> Nested Loop (cost=0.00..186249.55 rows=9404 width=8) (actual
time=11263.087..11263.087 rows=0 loops=1)
Why is planner usin
lars wrote:
> vacuum analyze;
I tried this out on a 16 core, 64 GB machine. It was a replication
target for a few dozen source databases into a couple 2 TB reporting
databases, and had some light testing going on, but it was only at
about 50% capacity, so that shouldn't throw this off by *too*
On Tue, Jul 12, 2011 at 2:08 PM, Kevin Grittner
wrote:
> lars wrote:
>
>> select count(*) from test where tenant = $1 and created_date = $2
>
> Ah, that might be a major clue -- prepared statements.
I'm really skeptical that this is the case -- the table is 100m and
there is no way you are bangi
On 07/12/2011 01:04 PM, lars wrote:
On 07/12/2011 12:08 PM, Kevin Grittner wrote:
lars wrote:
select count(*) from test where tenant = $1 and created_date = $2
Ah, that might be a major clue -- prepared statements.
What sort of a plan do you get for that as a prepared statement?
(Note, it
lars wrote:
> So a read of a row *will* trigger dead tuple pruning, and that
> requires WAL logging, and this is known/expected?
Yes, because pruning dead line pointers will make subsequent reads
faster. It's intended to be an optimization.
> This is actually the only answer I am looking fo
On 07/12/2011 12:08 PM, Kevin Grittner wrote:
lars wrote:
select count(*) from test where tenant = $1 and created_date = $2
Ah, that might be a major clue -- prepared statements.
What sort of a plan do you get for that as a prepared statement?
(Note, it is very likely *not* to be the same p
Mario Splivalo writes:
> Limit (cost=0.00..415.91 rows=21 width=8) (actual
> time=11263.089..11263.089 rows=0 loops=1)
> -> Nested Loop (cost=0.00..186249.55 rows=9404 width=8) (actual
> time=11263.087..11263.087 rows=0 loops=1)
> Why is planner using NestedLoops,
Because it thinks th
lars wrote:
> select count(*) from test where tenant = $1 and created_date = $2
Thinking about this some more, it would be interesting to know your
PostgreSQL configuration. I seem to remember you mentioning some
settings up-thread, but I'm not sure whether it was comprehensive.
Could you pa
lars wrote:
> select count(*) from test where tenant = $1 and created_date = $2
Ah, that might be a major clue -- prepared statements.
What sort of a plan do you get for that as a prepared statement?
(Note, it is very likely *not* to be the same plan as you get if you
run with literal value
On 07/12/2011 08:13 AM, Ivan Voras wrote:
On 12/07/2011 02:09, lars wrote:
Oh, and iowait hovers around 20% when SELECTs are slow:
avg-cpu: %user %nice %system %iowait %steal %idle
1.54 0.00 0.98 18.49 0.07 78.92
When SELECTs are fast it looks like this:
avg-cpu: %user %nice %system %iowait %
Hi, all.
I have a query, looking like this:
SELECT
pub_date
FROM
tubesite_object
INNER JOIN tubesite_image
ON tubesite_image.object_ptr_id = tubesite_object.id
WHERE
tubesite_object.site_id = 8
AND tubesite_object.pub_date < E'2011-07-12 13:
On Tue, Jul 12, 2011 at 9:36 AM, Kevin Grittner
wrote:
> lars wrote:
>
>> I am not trying to optimize this particular use case, but rather
>> to understand what Postgres is doing, and why SELECT queries are
>> affected negatively (sometimes severely) by concurrent (or even
>> preceding) UPDATEs a
Hello,
Here is an update on my problem :
- the problem was caused by "VACUUM ANALYZE", but by a plain "VACUUM" ;
- it was exactly the same with manual and automatic "VACUUM ANALYZE" ;
- it was caused by a GIN index on a tsvector, using a very high (1)
statistics target.
Setting back the
On 12/07/2011 02:09, lars wrote:
Oh, and iowait hovers around 20% when SELECTs are slow:
avg-cpu: %user %nice %system %iowait %steal %idle
1.54 0.00 0.98 18.49 0.07 78.92
When SELECTs are fast it looks like this:
avg-cpu: %user %nice %system %iowait %steal %idle
8.72 0.00 0.26 0.00 0.00 91.01
On 12/07/2011 16:18, Merlin Moncure wrote:
On Tue, Jul 12, 2011 at 8:22 AM, Ivan Voras wrote:
On 08/07/2011 01:56, lars wrote:
Setup:
PostgreSQL 9.1beta2 on a high memory (~68gb, 12 cores) EC2 Linux
instance (kernel 2.6.35) with the database and
WAL residing on the same EBS volume with EXT4 (
lars wrote:
> I am not trying to optimize this particular use case, but rather
> to understand what Postgres is doing, and why SELECT queries are
> affected negatively (sometimes severely) by concurrent (or even
> preceding) UPDATEs at all when the database resides in the cache
> completely.
I
On Tue, Jul 12, 2011 at 8:22 AM, Ivan Voras wrote:
> On 08/07/2011 01:56, lars wrote:
>
>> Setup:
>> PostgreSQL 9.1beta2 on a high memory (~68gb, 12 cores) EC2 Linux
>> instance (kernel 2.6.35) with the database and
>> WAL residing on the same EBS volume with EXT4 (data=ordered, barriers=1)
>> - y
On 08/07/2011 01:56, lars wrote:
Setup:
PostgreSQL 9.1beta2 on a high memory (~68gb, 12 cores) EC2 Linux
instance (kernel 2.6.35) with the database and
WAL residing on the same EBS volume with EXT4 (data=ordered, barriers=1)
- yes that is not an ideal setup
(WAL should be on separate drive, EBS
27 matches
Mail list logo
