On Tue, Oct 26, 2010 at 7:25 AM, Robert Haas wrote:
> On Tue, Oct 26, 2010 at 10:13 AM, Rob Wultsch wrote:
>> The double write buffer is one of the few areas where InnoDB does more
>> IO (in the form of fsynch's) than PG. InnoDB also has fuzzy
>> checkpoints (which help to keep dirty pages in mem
Robert Haas writes:
> On Tue, Oct 19, 2010 at 10:36 AM, Tom Lane wrote:
>> Those lseeks are for the purpose of detecting the current EOF
>> location, ie, finding out whether some other backend has extended the
>> file recently. We could get rid of them, but only at the cost of
>> putting in some
Robert Haas writes:
> I'm also a bit suspicious of the fact that the hash condition has a
> cast to text on both sides, which implies, to me anyway, that the
> underlying data types are not text. That might mean that the query
> planner doesn't have very good statistics, which might mean that the
On Tue, Oct 19, 2010 at 10:36 AM, Tom Lane wrote:
> Jon Nelson writes:
>> This is another situation where using pread would have saved a lot of
>> time and sped things up a bit, but failing that, keeping track of the
>> file position ourselves and only lseek'ing when necessary would also
>> help.
On Mon, Oct 18, 2010 at 9:40 PM, Scott Carey wrote:
> 8.4.5
>
> I consistently see HashJoin plans that hash the large table, and scan the
> small table. This is especially puzzling in some cases where I have 30M rows
> in the big table and ~ 100 in the small... shouldn't it hash the small table
On Tue, Oct 26, 2010 at 6:51 PM, Tom Lane wrote:
> Robert Haas writes:
>> I don't think this is due to fillfactor - the default fillfactor is
>> 100, and anyway we ARE larger on disk than Oracle. We really need to
>> do something about that, in the changes to NUMERIC in 9.1 are a step
>> in that
On Tue, Oct 26, 2010 at 6:18 PM, Ivan Voras wrote:
> FWIW, yes - once the IO is fast enough or not necessary (e.g. the
> read-mostly database fits in RAM), RAM bandwidth *is* the next bottleneck
> and it really, really can be observed in actual loads. Buying a QPI-based
> CPU instead of the cheape
On 10/27/10 01:45, James Cloos wrote:
"JB" == Josh Berkus writes:
JB> In a general workload, fewer faster cores are better. We do not scale
JB> perfectly across cores. The only case where that's not true is
JB> maintaining lots of idle connections, and that's really better dealt
JB> with
> "JB" == Josh Berkus writes:
JB> In a general workload, fewer faster cores are better. We do not scale
JB> perfectly across cores. The only case where that's not true is
JB> maintaining lots of idle connections, and that's really better dealt
JB> with in software.
I've found that ram spee
I have the following query:
select distinct Region.RegionShort as RegionShort
,County.County as County
from Region
join PostalCodeRegionCountyCity on
(PostalCodeRegionCountyCity.RegionId=Region.RegionId)
join DealerGroupGeoCache on
(DealerGroupGeoCache.RegionId=PostalCodeRegionCountyCity
On Tue, Oct 26, 2010 at 6:50 PM, Robert Haas wrote:
> On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala
> wrote:
>> The table is created with "on commit obliterate rows" option which means
>> that there is no need to do "truncate". The "truncate" command is a heavy
>> artillery. Truncating a tempora
Robert Haas writes:
> I don't think this is due to fillfactor - the default fillfactor is
> 100, and anyway we ARE larger on disk than Oracle. We really need to
> do something about that, in the changes to NUMERIC in 9.1 are a step
> in that direction, but I think a lot more work is needed.
Of c
On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala
wrote:
> The table is created with "on commit obliterate rows" option which means
> that there is no need to do "truncate". The "truncate" command is a heavy
> artillery. Truncating a temporary table is like shooting ducks in a duck
> pond, with a how
On Sat, Oct 16, 2010 at 2:44 PM, Kenneth Marshall wrote:
> Interesting data points. The amount of rows that you managed to
> insert into PostgreSQL before Oracle gave up the ghost is 95%
> of the rows in the Oracle version of the database. To count 5%
> fewer rows, it took PostgreSQL 24 seconds lo
On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala
wrote:
> On 10/26/2010 5:27 PM, Jon Nelson wrote:
>>
>> start loop:
>> populate rows in temporary table
>> insert from temporary table into permanent table
>> truncate temporary table
>> loop
>>
>> I do something similar, where I COPY data to
On 10/26/2010 5:27 PM, Jon Nelson wrote:
start loop:
populate rows in temporary table
insert from temporary table into permanent table
truncate temporary table
loop
I do something similar, where I COPY data to a temporary table, do
lots of manipulations, and then perform a series of
On Tue, Oct 26, 2010 at 4:02 PM, Mladen Gogala
wrote:
> On 10/26/2010 11:41 AM, Merlin Moncure wrote:
>>
>> yup, that's exactly what I mean -- this will give you more uniform
>> insert performance (your temp table doesn't even need indexes). Every
>> N records (say 1) you send to permanent an
- Mensagem original -
| On 10-10-25 03:26 PM, André Volpato wrote:
| > | On Mon, Oct 25, 2010 at 2:21 PM, André Volpato
| > | wrote:
(...)
| > |> These times keep repeating after the second run, and I can
| > |> ensure AIX isn´t touching the disks anymore.
| > |> I´ve never seen this b
On 10/26/2010 11:41 AM, Merlin Moncure wrote:
yup, that's exactly what I mean -- this will give you more uniform
insert performance (your temp table doesn't even need indexes). Every
N records (say 1) you send to permanent and truncate the temp
table. Obviously, this is more fragile approac
On 10/26/10 7:50 AM, Scott Marlowe wrote:
> For faster but fewer individual cores the Intels are pretty good. For
> way more cores, each being pretty fast and having enough memory
> bandwidth to use all those cores, the AMDs are very impressive. The
> Magny Cours AMDs are probably the best 4 sock
On Tue, Oct 26, 2010 at 11:08 AM, Leonardo Francalanci wrote:
>> temp tables are not wal logged or
>> synced. Periodically they can be flushed to a permanent table.
>
>
> What do you mean with "Periodically they can be flushed to
> a permanent table"? Just doing
>
> insert into tabb select * f
On 2010-10-26 16:27, Kevin Grittner wrote:
Christian Elmerot wrote:
What is the general view of performance CPU's nowadays when it
comes to PostgreSQL performance? Which CPU is the better choice,
in regards to RAM access-times, stream speed, cache
synchronization etc. Which is the better CPU g
> temp tables are not wal logged or
> synced. Periodically they can be flushed to a permanent table.
What do you mean with "Periodically they can be flushed to
a permanent table"? Just doing
insert into tabb select * from temptable
or using a proper, per-temporary table command???
--
On Tue, Oct 26, 2010 at 6:55 AM, Christian Elmerot wrote:
> Hello,
>
> What is the general view of performance CPU's nowadays when it comes to
> PostgreSQL performance? Which CPU is the better choice, in regards to RAM
> access-times, stream speed, cache synchronization etc. Which is the better
>
Christian Elmerot wrote:
> What is the general view of performance CPU's nowadays when it
> comes to PostgreSQL performance? Which CPU is the better choice,
> in regards to RAM access-times, stream speed, cache
> synchronization etc. Which is the better CPU given the limitation
> of using AMD64
On Tue, Oct 26, 2010 at 10:13 AM, Rob Wultsch wrote:
> The double write buffer is one of the few areas where InnoDB does more
> IO (in the form of fsynch's) than PG. InnoDB also has fuzzy
> checkpoints (which help to keep dirty pages in memory longer),
> buffering of writing out changes to seconda
On Tue, Oct 26, 2010 at 5:41 AM, Robert Haas wrote:
> On Fri, Oct 22, 2010 at 3:05 PM, Kevin Grittner
> wrote:
>> Rob Wultsch wrote:
>>
>>> I would think full_page_writes=off + double write buffer should be
>>> far superior, particularly given that the WAL is shipped over the
>>> network to slav
On 10-10-25 03:26 PM, André Volpato wrote:
| On Mon, Oct 25, 2010 at 2:21 PM, André Volpato
| wrote:
|> Hi all,
|>
|> We are tuning a PostgreSQL box with AIX 5.3 and got stucked in a
|> very odd situation.
|> When a query got ran for the second time, the system seems to
|> deliver the resul
On Tue, Oct 26, 2010 at 7:44 AM, Divakar Singh wrote:
> Hi Merlin,
> Thanks for your quick input.
> Well 1 difference worth mentioning:
> I am inserting each row in a separate transaction, due to design of my
> program.
Well, that right there is going to define your application
performance. You h
Hello,
What is the general view of performance CPU's nowadays when it comes to
PostgreSQL performance? Which CPU is the better choice, in regards to
RAM access-times, stream speed, cache synchronization etc. Which is the
better CPU given the limitation of using AMD64 (x86-64)?
We're getting
On Fri, Oct 22, 2010 at 3:05 PM, Kevin Grittner
wrote:
> Rob Wultsch wrote:
>
>> I would think full_page_writes=off + double write buffer should be
>> far superior, particularly given that the WAL is shipped over the
>> network to slaves.
>
> For a reasonably brief description of InnoDB double wr
On 10/26/2010 6:56 AM, AI Rumman wrote:
Which one is faster?
select count(*) from talble
or
select count(id) from table
where id is the primary key.
PostgreSQL doesn't utilize the access methods known as "FULL INDEX SCAN"
and "FAST FULL INDEX SCAN", so the optimizer will generate the
sequential
On Wed, Oct 13, 2010 at 5:20 PM, Joe Miller wrote:
> Thanks for fixing the docs, but if that's the case, I shouldn't be
> seeing the behavior that I'm seeing.
>
> Should I flesh out this test case a little better and file a bug?
A reproducible test case is always a good thing to have...
--
Robe
On Sat, Oct 16, 2010 at 1:22 AM, Tom Lane wrote:
> Alvaro Herrera writes:
>> If we analyze the parent, do we also update the children stats, or is it
>> just that we keep two stats for the parent, one with children and one
>> without, both being updated when the parent is analyzed?
>
> The latter
2010/10/26 Grzegorz Jaśkiewicz
> 2010/10/26 Szymon Guz :
> >
> > Well, strange. Why is that slower?
>
> To answer that fully, you would need to see the implementation.
> suffice to say,
>
> count(a) does:
>
> if (a <> NULL)
> {
> count++;
> }
>
> and count(*) does:
>
> count++;
>
>
>
Yup, I was
2010/10/26 Szymon Guz :
>
> Well, strange. Why is that slower?
To answer that fully, you would need to see the implementation.
suffice to say,
count(a) does:
if (a <> NULL)
{
count++;
}
and count(*) does:
count++;
--
GJ
--
Sent via pgsql-performance mailing list (pgsql-performance@po
2010/10/26 Grzegorz Jaśkiewicz
> implementation wise, count(*) is faster. Very easy to test:
>
> SELECT COUNT(*) FROM generate_series(1,100) a, generate_series(1,1000) b;
>
> SELECT COUNT(a) FROM generate_series(1,100) a, generate_series(1,1000) b;
>
>
> ;]
>
Well, strange. Why is that slower?
implementation wise, count(*) is faster. Very easy to test:
SELECT COUNT(*) FROM generate_series(1,100) a, generate_series(1,1000) b;
SELECT COUNT(a) FROM generate_series(1,100) a, generate_series(1,1000) b;
;]
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To m
2010/10/26 Marcin Mirosław
> W dniu 26.10.2010 12:59, Szymon Guz pisze:
> > both queries are the same.
>
> IMHO they aren't the same, but they returns the same value in this case.
> I mean count(field) doesn't count NULL values, count(*) does it.
> I'm writing this only for note:)
> Regards
>
>
Y
W dniu 26.10.2010 12:59, Szymon Guz pisze:
> both queries are the same.
IMHO they aren't the same, but they returns the same value in this case.
I mean count(field) doesn't count NULL values, count(*) does it.
I'm writing this only for note:)
Regards
--
Sent via pgsql-performance mailing list (p
Hi Merlin,
Thanks for your quick input.
Well 1 difference worth mentioning:
I am inserting each row in a separate transaction, due to design of my program.
-Divakar
From: Merlin Moncure
To: Divakar Singh
Cc: pgsql-performance@postgresql.org
Sent: Tue, Octob
On 26 October 2010 12:56, AI Rumman wrote:
> Which one is faster?
> select count(*) from talble
> or
> select count(id) from table
> where id is the primary key.
>
Check the query plan, both queries are the same.
regards
Szymon
Which one is faster?
select count(*) from talble
or
select count(id) from table
where id is the primary key.
On Oct 22, 2010, at 1:06 PM, Rob Wultsch wrote:
> On Fri, Oct 22, 2010 at 12:05 PM, Kevin Grittner
> wrote:
>> Rob Wultsch wrote:
>>
>>> I would think full_page_writes=off + double write buffer should be
>>> far superior, particularly given that the WAL is shipped over the
>>> network to slave
44 matches
Mail list logo
