On Tue, Apr 25, 2006 at 11:07:17PM -0400, Ron Peacetree wrote:
> THROUGHPUT is better with DDR2 if and only if there is enough data
> to be fetched in a serial fashion from memory.
> LATENCY however is dependent on the base clock rate of the RAM
> involved. So PC3200, 200MHz x2, is going to actual
On Apr 25, 2006, at 19:36, Tom Lane wrote:
It looks like you had something trivial as the definition of foo().
Yeah, the function call. :-)
Try one of the actual queries from the plpgsql function.
Oh. Duh. Will do. Tomorrow.
Best,
David
---(end of broadcast)
>Another benefit of Pentium D over AMD X2, at least until AMD chooses
>to switch, is that Pentium D supports DDR2, whereas AMD only supports
>DDR. There are a lot of technical pros and cons to each - with claims
>from AMD that DDR2 can be slower than DDR - but one claim that isn't
>often made, but
David Wheeler <[EMAIL PROTECTED]> writes:
> Just on a lark, I tried to get this to work:
> try=# explain analyze EXECUTE foo(1, ARRAY
> [61,62,63,64,65,66,67]);
>QUERY PLAN
> --
On Apr 25, 2006, at 18:19, Tom Lane wrote:
You'd really have to look at the plans generated for each of the
commands in the functions to be sure. A knee-jerk reaction is to
suggest that that NOT IN might be the core of the problem, but it's
only a guess.
Well, the rows are indexed (I forgot t
[EMAIL PROTECTED] wrote:
Another benefit of Pentium D over AMD X2, at least until AMD chooses
to switch, is that Pentium D supports DDR2, whereas AMD only supports
DDR. There are a lot of technical pros and cons to each - with claims
from AMD that DDR2 can be slower than DDR - but one claim that
"andremachado" <[EMAIL PROTECTED]> writes:
> After some time experimenting on windows, the conclusion is clear:
> windows is likely crap for databases other than MS-SQL.
Maybe. One thing that comes to mind is that you really should do some
performance tuning experiments. In particular it'd be a
> OK. Stop and think about what you're telling postgresql to do here.
>
> You're telling it to cast the field group_id to int8, then compare it to
> 9. How can it cast the group_id to int8 without fetching it? That's
> right, you're ensuring a seq scan. You need to put the int8 cast on the
> ot
David Wheeler <[EMAIL PROTECTED]> writes:
> This post is longish and has a bit of code, but here's my question up-
> front: Why are batch queries in my PL/pgSQL functions no faster than
> iterating over a loop and executing a series of queries for each
> iteration of the loop?
You'd really ha
On Tue, Apr 25, 2006 at 08:54:40PM -0400, [EMAIL PROTECTED] wrote:
> I made the choice I describe based on a lot of research. I was going
> to go both Intel, until I noticed that the Intel prices were dropping
> fast. 30% price cut in 2 months. AMD didn't drop at all during the
> same time.
Errr..
"Junaili Lie" <[EMAIL PROTECTED]> writes:
> ie. delete from scenario where id=3D'1023' is very fast, but delete from
> scenario where id=3D'1099' is running forever.
What does EXPLAIN show for each of those cases?
regards, tom lane
---(end of broad
On Tue, Apr 25, 2006 at 01:42:31PM -0500, Scott Marlowe wrote:
> On Tue, 2006-04-25 at 13:38, [EMAIL PROTECTED] wrote:
> > On Tue, Apr 25, 2006 at 01:33:38PM -0500, Scott Marlowe wrote:
> > > Sad, cause the AMD is, on a price / performance scale, twice the
> > > processor for the same money as the
hi,
Thanks for the answer.
I have double checked that all the foreign key that are referencing "id" on scenario are indexed.
I have even vacuum analyze scenario table and all the tables that referenced this table.
Something that is interesting is that: it only happens for a certain values.
ie. del
I did double check for indexes on the referenced and referencing
columns, and even though this database is restored and vacuum
analyzed nightly the issue remains. Using explain analyze in
postgresql 8.1, I was able to see where the problem lies. For
performance reasons on our 7.4 server,
On Tue, Apr 25, 2006 at 01:33:38PM -0500, Scott Marlowe wrote:
> On Tue, 2006-04-25 at 13:14, Bill Moran wrote:
> > I've been given the task of making some hardware recommendations for
> > the next round of server purchases. The machines to be purchased
> > will be running FreeBSD & PostgreSQL.
>
"Junaili Lie" <[EMAIL PROTECTED]> writes:
> we encounter issues when deleting from a table based on id (primary key). O=
> n
> certain 'id', it took forever to delete and the i/o is 100% busy.
Almost always, if delete is slow when selecting the same rows is fast,
it's because you've got a trigger
Ron Peacetree wrote:
As others have noted, the current price/performance "sweet spot" for DB servers
is 2S 2C AMD CPUs. These CPUs are also the highest performing x86 compatible solution
for pg.
If you must go Intel for some reason, then wait until the new NGMA CPU's
(Conroe, Merom, Woodcres
I should also mention that select ... for update is fast:
MONSOON=# begin;explain analyze select * from SCENARIO WHERE id = '1099' FOR UPDATE;
BEGIN
QUERY PLAN
I've had intermittent "freeze and reboot" and, worse, just plain freeze
problems with the Core Duo's I've been testing. I have not been able to narrow
it down so I do not know if it is a platform issue or a CPU issue. It appears
to be HW, not SW, related since I have experienced the problem bo
Hi all,
we encounter issues when deleting from a table based on id (primary
key). On certain 'id', it took forever to delete and the i/o is 100%
busy.
Table scenario has around 1400 entries. It is the parent of 3 other table.
Table "public.scenario"
Column
My personal favorite pg platform at this time is one based on a 2 socket, dual
core ready mainboard with 16 DIMM slots combined with dual core AMD Kx's.
Right. We've been buying Tyan bare-bones boxes like this.
It's better to go with bare-bones than building boxes from bare metal
because th
As others have noted, the current price/performance "sweet spot" for DB servers
is 2S 2C AMD CPUs. These CPUs are also the highest performing x86 compatible
solution for pg.
If you must go Intel for some reason, then wait until the new NGMA CPU's
(Conroe, Merom, Woodcrest) come out and see how
Joshua D. Drake wrote:
> David Boreham wrote:
> >
> >> Actually, that was from an article from this last month that compared
> >> the dual core intel to the amd. for every dollar spent on the intel,
> >> you got about half the performance of the amd. Not bigotry. fact.
> >>
> >> But don't belie
David Boreham wrote:
Actually, that was from an article from this last month that compared
the dual core intel to the amd. for every dollar spent on the intel,
you got about half the performance of the amd. Not bigotry. fact.
But don't believe me or the other people who've seen the differen
Actually, that was from an article from this last month that compared
the dual core intel to the amd. for every dollar spent on the intel,
you got about half the performance of the amd. Not bigotry. fact.
But don't believe me or the other people who've seen the difference. Go
buy the I
* Sriram Dandapani:
> Does the inet data type offer comparison/search performance benefits
> over plain text for ip addresses..
Queries like "host << '192.168.17.192/28'" use an available index on
the host column. In theory, you could do this with LIKE and strings,
but this gets pretty messy and
But don't believe me or the other people who've seen the difference. Go
buy the Intel box. No skin off my back.
To be more detailed... AMD Opteron has some specific technical
advantages to their design over Intel when it comes to peforming for a
database. Specifically no front side bus :)
Using an index on col1 with the operator class varchar_pattern_ops , I
was able to get a 3 second response time. That will work for me.
I used a like '172.%' and an extra pattern matching condition to
restrict
Between 172.16.x.x and 172.31.x.x
Thanks for the input..I will also test the inet data t
Bill Moran wrote:
I've been given the task of making some hardware recommendations for
the next round of server purchases. The machines to be purchased
will be running FreeBSD & PostgreSQL.
Where I'm stuck is in deciding whether we want to go with dual-core
pentiums with 2M cache, or with HT pe
On Tue, 2006-04-25 at 13:38, [EMAIL PROTECTED] wrote:
> On Tue, Apr 25, 2006 at 01:33:38PM -0500, Scott Marlowe wrote:
> > Sad, cause the AMD is, on a price / performance scale, twice the
> > processor for the same money as the Intel.
>
> Maybe a year or two ago. Prices are all coming down. Intel
On Tue, Apr 25, 2006 at 01:33:38PM -0500, Scott Marlowe wrote:
> Sad, cause the AMD is, on a price / performance scale, twice the
> processor for the same money as the Intel.
Maybe a year or two ago. Prices are all coming down. Intel more
than AMD.
AMD still seems better - but not X2, and it depe
On Tue, 2006-04-25 at 13:14, Bill Moran wrote:
> I've been given the task of making some hardware recommendations for
> the next round of server purchases. The machines to be purchased
> will be running FreeBSD & PostgreSQL.
>
> Where I'm stuck is in deciding whether we want to go with dual-core
On Tue, Apr 25, 2006 at 07:53:15PM +0200, PFC wrote:
What version is this??
> annonces=> EXPLAIN ANALYZE SELECT * FROM test.current WHERE id IN (SELECT
> annonce_id FROM bookmarks WHERE list_id IN ('4'));
>QUERY PLAN
> -
On Tue, 25 Apr 2006 14:14:35 -0400
Bill Moran <[EMAIL PROTECTED]> wrote:
> Does anyone in the PostgreSQL community have any experience with
> large caches or dual-core pentiums that could make any
> recommendations?
Heh :) You're in the position I was in about a year ago - we "naturally"
replace
On Tue, 2006-04-25 at 13:14, Bill Moran wrote:
> I've been given the task of making some hardware recommendations for
> the next round of server purchases. The machines to be purchased
> will be running FreeBSD & PostgreSQL.
>
> Where I'm stuck is in deciding whether we want to go with dual-core
The col is a varchar. I am currently testing with the inet data type(and
also the ipv4 pgfoundry data type).
Due to time constraints, I am trying to minimize code changes.
What kind of index do I need to create to enable efficient range scans
(e.g anything between 172.16.x.x thru 172.31.x.x) on t
On Tue, Apr 25, 2006 at 10:08:02AM -0700, Sriram Dandapani wrote:
Here's the key:
> " Filter: ((col1)::text ~~ '172.%'::text)"
In order to do a like comparison, it has to convert col1 (which I'm
guessing is of type 'inet') to text, so there's no way it can use an
index on col1 (maybe a function
PFC <[EMAIL PROTECTED]> writes:
> The IN() is quite small (150 values), but the two large tables are
> seq-scanned... is there a way to avoid this ?
Not in 8.1. HEAD is a bit smarter about joins to Append relations.
regards, tom lane
---(e
I've been given the task of making some hardware recommendations for
the next round of server purchases. The machines to be purchased
will be running FreeBSD & PostgreSQL.
Where I'm stuck is in deciding whether we want to go with dual-core
pentiums with 2M cache, or with HT pentiums with 8M cach
Title: Message
If you
are using a locale other than the C locale, you need to create the index with an
operator class to get index scans with like.
See
here for details:
http://www.postgresql.org/docs/8.1/interactive/indexes-opclass.html
-Original Message-From:
[EMAIL P
Here is a simple test case for this strange behaviour :
annonces=> CREATE TABLE test.current (id INTEGER PRIMARY KEY, description
TEXT);
INFO: CREATE TABLE / PRIMARY KEY creera un index implicite
<> pour la table <>
CREATE TABLE
annonces=> CREATE TABLE test.archive (id INTEGER PRI
For the query
Select col1 from table1
Where col1 like ‘172.%’
The table has 133 million unique ip addresses. Col1 is
indexed.
The optimizer is using a sequential scan
This is the explain analyze output
"Seq Scan on table1 (cost=0.00..2529284.80 rows=1
width=15) (actu
Hello,
The performance comparison saga of the last month continues (see list archive).
After some time experimenting on windows, the conclusion is clear:
windows is likely crap for databases other than MS-SQL.
I guess that MS-SQL uses lot of undocumented api calls, may run in kernel
mode, ring 0
Fellow PostgreSQLers,
This post is longish and has a bit of code, but here's my question up-
front: Why are batch queries in my PL/pgSQL functions no faster than
iterating over a loop and executing a series of queries for each
iteration of the loop? Are batch queries or array or series
gen
I have done the same tests on 8.1.0.
espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
agenda_users_groups WHERE group_id = 9;
QUERY PLAN
Arnau <[EMAIL PROTECTED]> writes:
> I have done the same tests on 8.1.0.
Bitmap scans are a totally different animal that doesn't exist in 7.4.
A plain indexscan, such as 7.4 knows about, is generally not effective
for fetching more than a percent or two of the table. The crossover
point for a bi
On Tue, 2006-04-25 at 10:47, Arnau wrote:
> Tom Lane wrote:
> > Arnau <[EMAIL PROTECTED]> writes:
> >
> >
> >>espsm_moviltelevision=# select count(*) from agenda_users_groups ;
> >> count
> >>-
> >> 2547556
> >
> >
> > So the SELECT is fetching nearly 15% of the rows in the table. T
Tom Lane wrote:
Arnau <[EMAIL PROTECTED]> writes:
Seq Scan on agenda_users_groups (cost=0.00..53108.45 rows=339675
width=8) (actual time=916.903..5763.830 rows=367026 loops=1)
Filter: (group_id = 9::numeric)
Total runtime: 7259.861 ms
(3 filas)
espsm_moviltelevision=# select count(*
Arnau <[EMAIL PROTECTED]> writes:
> Seq Scan on agenda_users_groups (cost=0.00..53108.45 rows=339675
> width=8) (actual time=916.903..5763.830 rows=367026 loops=1)
> Filter: (group_id = 9::numeric)
> Total runtime: 7259.861 ms
> (3 filas)
> espsm_moviltelevision=# select count(*) from a
On Tue, 2006-04-25 at 08:49, Arnau wrote:
> chris smith wrote:
> > On 4/25/06, Arnau <[EMAIL PROTECTED]> wrote:
> >
> >>Hi all,
> >>
> >> I have the following running on postgresql version 7.4.2:
> >>
> >>CREATE SEQUENCE agenda_user_group_id_seq
> >>MINVALUE 1
> >>MAXVALUE 9223372036854775807
>
On 4/25/06, Arnau <[EMAIL PROTECTED]> wrote:
> espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
> agenda_users_groups
> espsm_moviltelevision-# WHERE group_id = '9';
> QUERY PLAN
> --
chris smith wrote:
On 4/25/06, Arnau <[EMAIL PROTECTED]> wrote:
Hi all,
I have the following running on postgresql version 7.4.2:
CREATE SEQUENCE agenda_user_group_id_seq
MINVALUE 1
MAXVALUE 9223372036854775807
CYCLE
INCREMENT 1
START 1;
CREATE TABLE AGENDA_USERS_GROUPS
(
AGENDA_USER_GROU
On 4/25/06, Arnau <[EMAIL PROTECTED]> wrote:
> Hi all,
>
>I have the following running on postgresql version 7.4.2:
>
> CREATE SEQUENCE agenda_user_group_id_seq
> MINVALUE 1
> MAXVALUE 9223372036854775807
> CYCLE
> INCREMENT 1
> START 1;
>
> CREATE TABLE AGENDA_USERS_GROUPS
> (
> AGENDA_USER_
Hi all,
I have the following running on postgresql version 7.4.2:
CREATE SEQUENCE agenda_user_group_id_seq
MINVALUE 1
MAXVALUE 9223372036854775807
CYCLE
INCREMENT 1
START 1;
CREATE TABLE AGENDA_USERS_GROUPS
(
AGENDA_USER_GROUP_ID INT8
CONSTRAINT pk_agndusrgrp_usergrou
Ramasamy wrote:
Hi Richard ,
Very good Day. Great information that you given to me.
Great glad you think it's useful. Oh, don't forget to cc: the
mailing-list - that's the convention around here.
I will try in your idea. Here I am using SQL server 2000(Even I can use SQL
Sever 2005 too if
Friends wrote:
Hi
I need to set security for row level but not based on Database user's
login. It should be based on the user table login. For the particular
user I need to allow only the particular records to access insert,
update delete and select.
Well, the data access stuff is all manageab
56 matches
Mail list logo
