On Tue, Apr 5, 2011 at 1:25 PM, Maria L. Wilson
wrote:
This bit:
> left outer join INVENTORY IV on GV.GRANULE_ID = IV.GRANULE_ID, INVSENSOR
> INVS
has both an explicit and an implicit join. This can constrain join
re-ordering in the planner. Can you change it to explicit joins only
and see if
I suspect your app is doing lots of tiny single-row queries instead of
efficiently batching things. It'll be wasting huge amounts of time
waiting for results. Even if every query is individually incredibly
fast, with the number of them you seem to be doing you'll lose a LOT of
time if you loop o
Hello everyone,
I have the following scenario:
There's a web service that updates some information in two tables,
every 5 minutes.
In order to do this it will issue a select on the tables, get some
data, think about it, and then update it if necessary.
Sometimes - about once every two
why even have multiple rows? just jam it all it there! :-D
LOL
But seriously, when using an ORM to stuff an object hierarchy into a
database, you usually get problems with class inheritance, and all
solutions suck more or less (ie, you get a zillion tables, with assorted
pile of JOINs,
On Tue, May 10, 2011 at 12:22 PM, Shaun Thomas wrote:
> On 05/10/2011 10:06 AM, Maciek Sakrejda wrote:
>
> I have 8-core server, I wanted to ask whether a query can be divided for
>>> multiple processors or cores, if it could be what to do in postgresql
>>>
>>
>> No, at this time (and for the fo
On Tue, May 10, 2011 at 2:20 PM, Maria L. Wilson
wrote:
> haven't tested a composite index
>
> invsensor is 2,003,980 rows and 219MB
> granver is 5,138,730 rows and 556MB
> the machine has 32G memory
> seq_page_cost, random_page_costs & effective_cache_size are set to the
> defaults (1,4, and 128M
On Tue, May 10, 2011 at 2:57 PM, Tomas Vondra wrote:
> Dne 10.5.2011 18:22, Shaun Thomas napsal(a):
> > On 05/10/2011 10:06 AM, Maciek Sakrejda wrote:
> >
> >>> I have 8-core server, I wanted to ask whether a query can be divided
> for
> >>> multiple processors or cores, if it could be what to do
Scott Marlowe writes:
> On Tue, Apr 5, 2011 at 1:25 PM, Maria L. Wilson
> wrote:
> This bit:
>> left outer join INVENTORY IV on GV.GRANULE_ID = IV.GRANULE_ID, INVSENSOR
>> INVS
> has both an explicit and an implicit join. This can constrain join
> re-ordering in the planner. Can you change it
On 05/11/2011 06:08 AM, Prodan, Andrei wrote:
Index Scan using attr_name_value on big_table (cost=0.00..22.85
rows=4 width=7) (actual time=0.176..757.646 rows=914786 loops=1)
Holy inaccurate statistics, Batman!
Try increasing your statistics target for attr_name and attr_value in
your big t
On 05/10/2011 11:26 PM, Scott Marlowe wrote:
I.e. don't grab 1,000 rows and work on them on the client side and
then insert data, do the data mangling in the query in the database.
My experience has been that moving things like this into the database
can result in performance gains of several fa
Shaun Thomas writes:
> On 05/11/2011 06:08 AM, Prodan, Andrei wrote:
>> Index Scan using attr_name_value on big_table (cost=0.00..22.85
>> rows=4 width=7) (actual time=0.176..757.646 rows=914786 loops=1)
> Holy inaccurate statistics, Batman!
> Try increasing your statistics target for attr_name
On Mon, Apr 11, 2011 at 4:11 PM, Lucas Madar wrote:
> I have a database that contains many tables, each with some common
> characteristics. For legacy reasons, they have to be implemented in a way so
> that they are *all* searchable by an older identifier to find the newer
> identifier. To do this
Robert Haas writes:
> A more interesting question is why you're not getting a plan like this:
> Nested Loop
> -> Seq Scan on objects
> -> Append
>-> Index Scan using xxx_pkey on itemXX
>-> Index Scan using yyy_pkey on itemYY
>-> Index Scan using zzz_pkey on itemZZ
Probably because
This is a problem I encounter constantly wherever I go. Programmer
selects millions of rows from giant table. Programmer loops through
results one by one doing some magic on them. Programmer submits queries
back to the database. Even in batches, that's going to take ages.
Reminds me of a
On Wed, May 11, 2011 at 4:08 AM, Prodan, Andrei
wrote:
>
...
>
>
> The select is as follows:
> prepare ps(varchar,varchar,varchar) as select party.party_id from party,
> big_table where external_id = $1 and party.party_id = big_table.party_id
> and attr_name = $2 and attr_value = $3;
> PREPARE
> e
Original message
>Date: Wed, 11 May 2011 11:04:49 -0500
>From: pgsql-performance-ow...@postgresql.org (on behalf of Shaun Thomas
>)
>Subject: Re: [PERFORM] Postgres refusing to use >1 core
>To: Scott Marlowe
>Cc: Craig Ringer ,Aren Cambre
>,
>
>On 05/10/2011 11:26 PM, Scott Marlowe w
On Wed, May 11, 2011 at 1:53 PM, wrote:
> So, the $64 question: how did you find an engagement where, to bend
> Shakespeare, "first thing we do, is kill all the coders" isn't required?
> This RBAR mentality, abetted by xml/NoSql/xBase, is utterly pervasive. They
> absolutely refuse to lear
On 05/11/2011 02:53 PM, gnuo...@rcn.com wrote:
So, the $64 question: how did you find an engagement where, to bend
Shakespeare, "first thing we do, is kill all the coders" isn't
required?
It's just one of those things you have to explain. Not just how to fix
it, but *why* doing so fixes it.
Original message
>Date: Wed, 11 May 2011 17:04:50 -0500
>From: pgsql-performance-ow...@postgresql.org (on behalf of Shaun Thomas
>)
>Subject: Re: [PERFORM] Postgres refusing to use >1 core
>To:
>Cc: Scott Marlowe ,Craig Ringer
>,Aren Cambre
>,
>
>On 05/11/2011 02:53 PM, gnuo...@rcn.
On 5/11/11 3:04 PM, Shaun Thomas wrote:
> The original query, with our very large tables, ran for over *two hours*
> thanks to a nested loop iterating over the subquery. My replacement ran
> in roughly 30 seconds. If we were using a newer version of PG, we could
> have used a CTE. But do you get wh
>
> I suspect your app is doing lots of tiny single-row queries instead of
> efficiently batching things. It'll be wasting huge amounts of time
> waiting for results. Even if every query is individually incredibly
> fast, with the number of them you seem to be doing you'll lose a LOT of
> time if y
>
> > Using one thread, the app can do about 111 rows per second, and it's
> > only exercising 1.5 of 8 CPU cores while doing this. 12,000,000 rows /
> > 111 rows per second ~= 30 hours.
>
> I don't know how I missed that. You ARE maxing out one cpu core, so
> you're quite right that you need more
>
>
> I suspect your app is doing lots of tiny single-row queries instead of
>> efficiently batching things. It'll be wasting huge amounts of time
>> waiting for results. Even if every query is individually incredibly
>> fast, with the number of them you seem to be doing you'll lose a LOT of
>> ti
On Wed, Apr 13, 2011 at 1:22 PM, Scott Carey wrote:
> A pathological skew case (all relations with the same key), should be
> _cheaper_ to probe. There should be only _one_ entry in the hash (for
> the one key), and that entry will be a list of all relations matching the
> key. Therefore, hash
On Tue, Apr 12, 2011 at 3:51 AM, Sethu Prasad wrote:
> Anyone lucky to have dbt5 run for PostgreSQL 9.0.3?!
>
> I am trying on Novell SuSE Linux Enterprise Server 11 SP1 x86_64 with a
> virtual machine and bit hard with no success run yet. If you can help me
> with any docs will be more of a suppo
On Wed, May 11, 2011 at 9:20 PM, Aren Cambre wrote:
>> Using unix sockets, you can expect about 10-20.000 queries/s on small
>> simple selects per core, which is quite a feat. TCP adds overhead, so it's
>> slower. Over a network, add ping time.
>
> I'm talking to a Postgres on localhost, so in the
On Fri, Apr 22, 2011 at 5:21 AM, drvillo wrote:
> -given the configuration attached (which is basically a vanilla one) and the
> number of buffers written at each execution, are these execution times
> normal or above average?
They seem fine. Remember that the write is deliberately spread out;
i
On Tue, Apr 26, 2011 at 11:15 AM, Joel Reymont wrote:
> I'm running pgsql on an m1.large EC2 instance with 7.5gb available memory.
>
> The free command shows 7gb of free+cached. My understand from the docs is
> that I should dedicate 1.75gb to shared_buffers (25%) and set
> effective_cache_size
On 5/11/2011 9:17 PM, Aren Cambre wrote:
So here's what's going on.
If I were doing this, considering the small size of the data set, I'd
read all the data into memory.
Process it entirely in memory (with threads to saturate all the
processors you have).
Then write the results to the DB.
29 matches
Mail list logo
