Re: [PERFORM] Very Poor Insert Performance
On Monday 27 October 2003 8:12 pm, Tom Lane wrote:
> Damien Dougan <[EMAIL PROTECTED]> writes:
> > Has anyone any ideas as to what could be causing the spiraling
> > performance?
>
> You really haven't provided any information that would allow anything
> but guesses, but I'll guess anyway: poor plans for foreign key checks?
> See nearby threads.
>
> regards, tom lane
Apologies for not including more info - I had been hoping that spiralling performance
was a known tell-tale sign of something :)
Here is some additional information - sorry if its overload, but I figured I should
give an intro to the schema before showing the EXPLAIN results!
Firstly, a quick schema overview for the relevant tables:
contact has many contactparts
address has many addressparts
contact has many address
Now, the above table relationships are connected via relationship tables (rather than
foreign indexes directly to each other), so we have:
contact
rel_contact_has_contactpart
address
rel_address_has_addresspart
(The reasons behind this are for meta-data purposes - our database is intended to be
very abstract from the code...)
Table "public.contact"
Column |Type | Modifiers
--+-+---
id | integer | default
nextval('contact_id_seq'::text)
version | integer | default 1
contactid| character varying |
enddate | timestamp without time zone |
preferredaddress | character varying |
startdate| timestamp without time zone |
Indexes:
"contact_id_idx" unique, btree (id)
"contact_key" unique, btree (contactid)
So we have an index on the meta-data related "id" and the externally visible
"contactid" values. The "id" is used with the rel_contact_has_XXX tables (see below).
Table "public.contactpart"
Column| Type| Modifiers
-+---+
id | integer | default nextval('contactpart_id_seq'::text)
version | integer | default 1
detailname | character varying | not null
detailvalue | character varying |
Indexes:
"contactpart_id_idx" unique, btree (id)
So we have an index on the meta-data related "id".
Table "public.address"
Column |Type | Modifiers
-+-+
id | integer | default nextval('mc_address_id_seq'::text)
version | integer | default 1
enddate | timestamp without time zone |
format | character varying |
type| character varying | not null
value | character varying |
Indexes:
"address_id_idx" unique, btree (id)
"address_value_key" btree (value)
So we have an index on the meta-data related "id".
Table "public.addresspart"
Column| Type| Modifiers
-+---+
id | integer | default nextval('addresspart_id_seq'::text)
version | integer | default 1
detailname | character varying | not null
detailvalue | character varying |
Indexes:
"addresspart_id_idx" unique, btree (id)
So we have an index on the meta-data related "id". This is used with the
rel_address_has_addresspart table (see below).
Table "public.rel_contact_has_contactpart"
Column| Type | Modifiers
--+-+---
contact_id | integer |
contactpart_id | integer |
Indexes:
"rel_contact_has_contactpart_idx2" unique, btree (contactpart_id)
"rel_contact_has_contactpart_idx1" btree (contact_id)
So we have a unique index on the contactpart and a non-unique index on the contact (to
reflect the 1:M relationship contact has contactparts)
Table "public.rel_address_has_addresspart"
Column | Type | Modifiers
---+-+---
address_id| integer |
addresspart_id| integer |
Indexes:
"rel_address_has_addresspart_idx2" unique, btree (addresspart_id)
"rel_address_has_addresspart_idx1" btree (address_id)
So we have a unique index on the addresspart and a non-unique index on the address (to
reflect the 1:M relationship address has addressparts)
Table "public.rel_contact_has_address"
Column| Type | Modifiers
--+-+---
contact_id | integer |
address_id | integer |
Indexes:
"rel_contact_has_address_idx2" unique, btree (address_id)
"r
Re: [PERFORM] Adding foreign key performance
On Wed, 29 Oct 2003 09:47:28 +0800 Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote: > >>Just loaded up delicious 7.4b5 and wow... > >> > >>sort_mem 8192: 137038ms [lots of tmp file activity] > >>sort_mem 256000: 83109ms > > > 1. A full ANALYZE > 2. A delete all from pg_statistic > I had previously analyze'd before I ran those numbers. But I did it again with and without stats. With: Run 1 Time: 80157.21 ms Run 2 Time: 80763.59 ms Killed statistics: Time: 80571.71 ms Time: 80759.18 ms Chances are it is going to seq scan regardless so the stats are rather useless. Perhaps in other scenarios it would help. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] redundent index?
I just noticed on one of my tables I have the following two indexes:
Indexes: entity_watch_map_pkey primary key btree (entity_id, watch_id),
ewm_entity_id btree (entity_id),
I can't think of why the second index is there, as ISTM there is no
instance where the first index wouldn't be used in place of the second
one if i were to delete the second one. its a heavily updated table, so
axing the second one would be a bonus for performance, am i missing
something? Thanks in advance,
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Very Poor Insert Performance
Damien Dougan <[EMAIL PROTECTED]> writes: > Now, our problem seems to be the delays introduced by reading from the > public views. Your initial message stated plainly that the problem was in INSERTs; it's not surprising that you got unhelpful advice. > View definition: > [ huge view full of LEFT JOINs ] > As you can see, the PublicView is resulting in a huge nested loop, > with an index scan of the contact only occurring at the end. I would > have expected something more like: > (1) An index scan of the contact table to determine the correct contact > (2) An index scan of the address table using the rel_contact_has_address.address_id > to obtain the (relatively small - max 16, and typically 2) addresses > (3) A number of joins - at the same level rather than looping - to obtain the > detailnames for the new column names of the public view Your LEFT JOINs are constraining the join order --- see http://www.postgresql.org/docs/7.3/static/explicit-joins.html You'll need to reorder the joins into something that does what you want. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Very Poor Insert Performance
On Wednesday 29 October 2003 2:23 pm, Tom Lane wrote: > Your initial message stated plainly that the problem was in INSERTs; > it's not surprising that you got unhelpful advice. But perhaps my use of the term "insert" to describe upload was a very bad call given the domain of the list... I assure you I wasn't setting out to deceive anyone! The only location i used INSERT (ie as a Postgres keyword) was towards the end of my mail when I tried to highlight the fact we couldn't use COPY to upload our data because of the difficulty in maintaining the code to generate inter-table relations ahead of time. The problem was showing itself during database upload - so I assumed (ASS out of U and ME and all that!) that the write delay was very large (hence the disappointing improvements by switching off fsync etc). It was only after further investigation that we discovered that simulated INSERTs were going fine, but the Read delays between INSERTs where holding us up. > Your LEFT JOINs are constraining the join order --- see > http://www.postgresql.org/docs/7.3/static/explicit-joins.html > You'll need to reorder the joins into something that does what you want. Thanks very much for the heads-up, we'll reorder the joins into something more effecient! Damien ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] redundent index?
On Wed, 2003-10-29 at 09:03, Robert Treat wrote: > I just noticed on one of my tables I have the following two indexes: > > Indexes: entity_watch_map_pkey primary key btree (entity_id, watch_id), > ewm_entity_id btree (entity_id), > > > I can't think of why the second index is there, as ISTM there is no > instance where the first index wouldn't be used in place of the second The cost in evaluating the first index will be a little higher (more data to pull off disk due to second item), so there may be a few borderline cases that could switch to a sequential scan rather than an index scan. signature.asc Description: This is a digitally signed message part
[PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
Dear PostgreSQL gurus,
I really not intend to start a flame war here but i am genuinely
seeking help to retain PostgreSQL as my database for my RT
system.
Few months back i had posted regarding lowering of column names in SQL
being passed to RDBMS by DBIx::SearchBuilder , looks like it was controlled
by a parameter "CASESENSITIVE" changing it to 1 from 0 did help for postgresql
to MySQL it probably does not matter.
But This time its a different situation
The query in Postgresql is taking 6 times more than MySQL
The Query being given gets generated by DBIx::SearchBuilder.
Although i am not sure but i feel modules like DBIx::SearchBuilder which are
supposed to provide RDBMS independent abstraction are unfortunately
getting test only with MySQL or Oracle otherwise such huge difference in timing
were not possible.
IN MYSQL:
mysql> SELECT DISTINCT main.* FROM Groups main , Principals Principals_1, ACL ACL_2
WHERE
((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( (
ACL_2.PrincipalId =
Principals_1.id AND ACL_2.PrincipalType = 'Group' AND ( main.Domain =
'SystemInternal' OR
main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') AND main.id =
Principals_1.id) OR (
( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR ( main.Domain =
'RT::Ticket-Role'
AND main.Instance = 6973) ) AND main.Type = ACL_2.PrincipalType AND main.id =
Principals_1.id) )
AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND
ACL_2.ObjectId = 25)
) ORDER BY main.Name ASC
;+---++---++---+--+
| id| Name | Description | Domain | Type |
Instance |
+---++---++---+--+
| 40208 | sales | Sales team in Delhi | UserDefined| |
|
| 2 | User 1 | ACL equiv. for user 1 | ACLEquivalence | UserEquiv | 1
|
|11 | User 10| ACL equiv. for user 10| ACLEquivalence | UserEquiv | 10
|
|13 | User 12| ACL equiv. for user 12| ACLEquivalence | UserEquiv | 12
|
| 31067 | User 31066 | ACL equiv. for user 31066 | ACLEquivalence | UserEquiv | 31066
|
+---++---++---+--+
5 rows in set (0.94 sec)
mysql>
WHEREAS for PostgreSQL:
rt3=# SELECT version();
PostgreSQL 7.4beta5 on i686-pc-linux-gnu, compiled by GCC 2.96
rt3=# SELECT DISTINCT main.* FROM Groups main , Principals Principals_1, ACL ACL_2
WHERE
((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( (
ACL_2.PrincipalId =
Principals_1.id AND ACL_2.PrincipalType = 'Group' AND ( main.Domain =
'SystemInternal' OR
main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') AND main.id =
Principals_1.id) OR (
( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR ( main.Domain =
'RT::Ticket-Role'
AND main.Instance = 6973) ) AND main.Type = ACL_2.PrincipalType AND main.id =
Principals_1.id) )
AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND
ACL_2.ObjectId = 25)
) ORDER BY main.Name ASC
;+---++---++---+--+
| id |name|description| domain | type|
instance |
+---++---++---+--+
| 40264 | sales | Sales team in Delhi | UserDefined| |
|
| 2 | User 1 | ACL equiv. for user 1 | ACLEquivalence | UserEquiv | 1
|
|11 | User 10| ACL equiv. for user 10| ACLEquivalence | UserEquiv | 10
|
|13 | User 12| ACL equiv. for user 12| ACLEquivalence | UserEquiv | 12
|
| 31123 | User 31122 | ACL equiv. for user 31122 | ACLEquivalence | UserEquiv | 31122
|
+---++---++---+--+
(5 rows)
Time: 7281.574 ms
rt3=#
Explain Analyze of Above Query is being given below:
Unique (cost=4744.06..4744.08 rows=1 width=81) (actual time=6179.789..6179.828 rows=5
loops=1)
-> Sort (cost=4744.06..4744.07 rows=1 width=81) (actual time=6179.785..6179.792
rows=6 loops=1)
Sort Key: main.name, main.id, main.description, main."domain", main."type",
main.instance
-> Nested Loop (cost=1788.68..4744.05 rows=1 width=81) (actual
time=584.004..6179.712
rows=6 loops=1) Join Filter: "inner".principaltype)::text =
'Group'::text) OR
(("outer"."domain")::text = 'RT::Ticket-Role'::text) OR
(("outer"."domain")::text =
'RT::Queue-Role'::text)) AND ((("inner".principaltype)::text =
'Group'::text) OR
(("outer".instance)::text = '6973'::text) OR (("outer"."domain")::text =
'RT::Queue-Role'::text)) AND ((("inner".
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
Actually PostgreSQL is at par with MySQL when the query is being Properly Written(simplified) like below rt3=# SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 using(id) join ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) WHERE ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( ( ACL_2.PrincipalType = 'Group' AND ( main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') ) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR ( main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type = ACL_2.PrincipalType ) ) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC ; id |name|description| domain | type| instance ---++---++---+-- 40264 | sales | Sales team in Delhi | UserDefined| | 2 | User 1 | ACL equiv. for user 1 | ACLEquivalence | UserEquiv | 1 11 | User 10| ACL equiv. for user 10| ACLEquivalence | UserEquiv | 10 13 | User 12| ACL equiv. for user 12| ACLEquivalence | UserEquiv | 12 31123 | User 31122 | ACL equiv. for user 31122 | ACLEquivalence | UserEquiv | 31122 (5 rows) ( Total runtime: 1.699 ms ) Time: 6.455 ms which is 0.00 6455 Secs In mysql: mysql> SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 using(id) join ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) WHERE ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( ( ACL_2.PrincipalType = 'Group' AND ( main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') ) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR ( main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type = ACL_2.PrincipalType ) ) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC ;+---++---++---+--+ | id| Name | Description | Domain | Type | Instance | +---++---++---+--+ | 40208 | sales | Sales team in Delhi | UserDefined| | | | 2 | User 1 | ACL equiv. for user 1 | ACLEquivalence | UserEquiv | 1 | |11 | User 10| ACL equiv. for user 10| ACLEquivalence | UserEquiv | 10 | |13 | User 12| ACL equiv. for user 12| ACLEquivalence | UserEquiv | 12 | | 31067 | User 31066 | ACL equiv. for user 31066 | ACLEquivalence | UserEquiv | 31066 | +---++---++---+--+ 5 rows in set (0.00 sec) mysql> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also. But the question is my does PostgreSQL suffer so badly ?? I think not all developers write very nice SQLs. Its really sad to see that a fine peice of work (RT) is performing sub-optimal becoz of malformed SQLs. [ specially on database of my choice ;-) ] Regds Mallah. > > Dear PostgreSQL gurus, > > I really not intend to start a flame war here but i am genuinely > seeking help to retain PostgreSQL as my database for my RT > system. > > Few months back i had posted regarding lowering of column names in SQL being passed > to RDBMS by > DBIx::SearchBuilder , looks like it was controlled by a parameter "CASESENSITIVE" > changing it > to 1 from 0 did help for postgresql to MySQL it probably does not matter. > > > But This time its a different situation > The query in Postgresql is taking 6 times more than MySQL > > The Query being given gets generated by DBIx::SearchBuilder. > Although i am not sure but i feel modules like DBIx::SearchBuilder which are > supposed to > provide RDBMS independent abstraction are unfortunately getting test only with > MySQL or Oracle > otherwise such huge difference in timing were not possible. > > > > IN MYSQL: > > mysql> SELECT DISTINCT main.* FROM Groups main , Principals Principals_1, ACL ACL_2 > WHERE > ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( ( > ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND ( > main.Domain = > 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') > AND main.id > = Principals_1.id) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) > OR ( > main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type = > ACL_2.PrincipalType AND main.id = Principals_1.id) ) AND (ACL_2.ObjectType = > 'RT::System' OR > (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC > ;+---++-
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
> So its not just PostgreSQL that is suffering from the bad SQL but > MySQL also. But the question is my does PostgreSQL suffer so badly > ?? I think not all developers write very nice SQLs. > > Its really sad to see that a fine peice of work (RT) is performing > sub-optimal becoz of malformed SQLs. [ specially on database of my > choice ;-) ] Post EXPLAIN ANALYZES of the queries you're running, then maybe you'll be able to get some useful help from this list. Until then, it's very hard to speculate as to why PostgreSQL is slower. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
> > > > On Thu, Oct 30, 2003 at 01:15:44AM +0530, [EMAIL PROTECTED] wrote: >> Actually PostgreSQL is at par with MySQL when the query is being Properly >> Written(simplified) >> >> In mysql: >> mysql> SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 >> using(id) join >> ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) > > Interesting, last time I looked, this syntax wasn't valid on mysql. And I'm not > familiar with > the "using(id)" notation. Can you point me at proper docs on it? I am using MySQL 4.0.16 the latest stable one. Docs MySQL: http://www.mysql.com/doc/en/JOIN.html Postgresql: well i am not able to point out a dedicated page for this topic in pgsql document but below covers it a bit. http://www.postgresql.org/docs/7.3/static/sql-select.html Join i beleive are SQL standard feature and better docs shud exist. > > >> >> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also. But >> the question >> is my does PostgreSQL suffer so badly ?? >> I think not all developers write very nice SQLs. >> >> Its really sad to see that a fine peice of work (RT) is performing sub-optimal >> becoz of >> malformed SQLs. [ specially on database of my choice ;-) ] > > Can you try using SearchBuilder 0.90? That made certain optimizations to the > postgres query > builder that got backed out in 0.92, due to a > possible really bad failure mode. Thankfully, because all of this is machine > generated SQL we > can just improve the generator, rather than having to retool the entire application. True, Its really a pleasure to see that in DBIx/SearchBuilder/Handle/Pg.pm Database Specific optimisations can be done easily Congratulations on writing SearchBuilder in such an well structured manner. mine is .92 just going to try .90 as u are suggesting and will post back the result. > > > -- > jesse reed vincent -- [EMAIL PROTECTED] -- [EMAIL PROTECTED] > 70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90 > > "If IBM _wanted_ to make clones, we could make them cheaper and faster than anyone > else!" - An > IBM Rep. visiting Vassar College's Comp Sci Department. - Over 1,00,000 exporters are waiting for your order! Click below to get in touch with leading Indian exporters listed in the premier trade directory Exporters Yellow Pages. http://www.trade-india.com/dyn/gdh/eyp/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
>> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also. But
>> the
>> question is my does PostgreSQL suffer so badly ?? I think not all developers write
>> very nice
>> SQLs.
>>
>> Its really sad to see that a fine peice of work (RT) is performing sub-optimal
>> becoz of
>> malformed SQLs. [ specially on database of my choice ;-) ]
>
> Post EXPLAIN ANALYZES of the queries you're running, then maybe you'll be able to
> get some
> useful help from this list. Until then, it's very hard to speculate as to why
> PostgreSQL is
> slower. -sc
Here It is:
in case they are illegeble please lemme know i will attach it as .txt
files.
Slower One:
explain analyze SELECT DISTINCT main.* FROM Groups main , Principals Principals_1, ACL
ACL_2
WHERE ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( (
ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND (
main.Domain =
'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') AND
main.id =
Principals_1.id) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR (
main.Domain
= 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type = ACL_2.PrincipalType
AND main.id
= Principals_1.id) ) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType =
'RT::Queue' AND
ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC ;
Unique (cost=4744.06..4744.08 rows=1 width=81) (actual time=6774.140..6774.204 rows=5
loops=1)
-> Sort (cost=4744.06..4744.07 rows=1 width=81) (actual time=6774.136..6774.145
rows=6 loops=1)
Sort Key: main.name, main.id, main.description, main."domain", main."type",
main.instance
-> Nested Loop (cost=1788.68..4744.05 rows=1 width=81) (actual
time=597.744..6774.042
rows=6 loops=1) Join Filter: "inner".principaltype)::text =
'Group'::text) OR
(("outer"."domain")::text = 'RT::Ticket-Role'::text) OR
(("outer"."domain")::text =
'RT::Queue-Role'::text)) AND ((("inner".principaltype)::text =
'Group'::text) OR
(("outer".instance)::text = '6973'::text) OR (("outer"."domain")::text =
'RT::Queue-Role'::text)) AND ((("inner".principaltype)::text =
'Group'::text) OR
(("outer"."domain")::text = 'RT::Ticket-Role'::text) OR
(("outer".instance)::text =
'25'::text)) AND ((("inner".principaltype)::text = 'Group'::text) OR
(("outer".instance)::text = '6973'::text) OR (("outer".instance)::text =
'25'::text)) AND ((("inner".principaltype)::text = 'Group'::text) OR
(("outer"."type")::text = ("inner".principaltype)::text)) AND
((("outer"."domain")::text = 'SystemInternal'::text) OR
(("outer"."domain")::text =
'UserDefined'::text) OR (("outer"."domain")::text =
'ACLEquivalence'::text) OR
(("outer"."type")::text = ("inner".principaltype)::text)) AND
(("inner".principalid
= "outer".id) OR (("outer"."domain")::text = 'RT::Ticket-Role'::text) OR
(("outer"."domain")::text = 'RT::Queue-Role'::text)) AND
(("inner".principalid =
"outer".id) OR (("outer".instance)::text = '6973'::text) OR
(("outer"."domain")::text = 'RT::Queue-Role'::text)) AND
(("inner".principalid =
"outer".id) OR (("outer"."domain")::text = 'RT::Ticket-Role'::text) OR
(("outer".instance)::text = '25'::text)) AND (("inner".principalid =
"outer".id) OR
(("outer".instance)::text = '6973'::text) OR (("outer".instance)::text =
'25'::text)) AND (("inner".principalid = "outer".id) OR
(("outer"."type")::text =
("inner".principaltype)::text)) AND (("outer".id = "outer".id) OR
(("outer"."type")::text = ("inner".principaltype)::text)) AND
(("inner".principalid
= "outer".id) OR ("outer".id = "outer".id)) AND
((("inner".principaltype)::text =
'Group'::text) OR ("outer".id = "outer".id))) -> Merge
Join (cost=1788.68..4735.71 rows=1 width=85) (actual
time=597.540..1340.526 rows=20153 loops=1) Merge
Cond: ("outer".id = "inner".id)
Join Filter: ((("inner".id = "outer".id) OR
(("inner"."domain")::text =
'RT::Ticket-Role'::text) OR (("inner"."domain")::text =
'RT::Queue-Role'::text)) AND (("inner".id = "outer".id) OR
(("inner".instance)::text = '6973'::text) OR
(("inner"."domain")::text =
'RT::Queue-Role'::text)) AND (("inner".id = "outer".id) OR
(("inner"."domain")::text = 'RT::Ticket-Role'::text) OR
(("inner".instance)::text = '25'::text)) AND (("inner".id =
"outer".id) OR
(("inner".instance)::text = '6973'::text) OR
(("inner".instance)::text =
'25'::t
[PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
ok this time it constructs a query which puts 7.3.4 on a infinite loop but 7.4b5 is able to come out of it. since it may be of interest to the pgsql people i am Ccing it to the pgsql-performance list i hope its ok. Pgsql 7.3.4 on an endless loop: SELECT DISTINCT main.* FROM Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId)) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId)) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id)) WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( ( ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') ) ) AND ( (main.Status = 'new')OR(main.Status = 'open') ) ) ORDER BY main.Priority DESC LIMIT 10 But 7.4 beta5 seems to be able to handle it: SELECT DISTINCT main.* FROM Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId)) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId)) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id)) WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( ( ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') ) ) AND ( (main.Status = 'new')OR(main.Status = 'open') ) ) ORDER BY main.Priority DESC LIMIT 10; id | effectiveid | queue | type | issuestatement | resolution | owner | subject | initialpriority | finalpriority | priority | timeestimated | timeworked | status | timeleft | told | starts| started | due | resolved | lastupdatedby | lastupdated | creator | created | disabled--+-+---++++---+-+-+---+--+---+++--+--+-+-+-+-+---+-+-+-+-- 13 | 13 |23 | ticket | 0 | 0 | 31122 | General Discussion | 0 | 0 |0 | 0 | 0 | new |0 | | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 31122 | 2001-11-22 04:19:10 | 31122 | 2001-11-22 04:19:07 |0 6018 |6018 |19 | ticket | 0 | 0 |10 | EYP Prospective Clients | 0 | 0 |0 | 0 | 0 | new| 0 | | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2002-09-11 18:29:37 | 1970-01-01 00:00:00 | 31122 | 2002-09-11 18:29:39 | 31122 | 2002-09-11 18:29:37 | 0 6336 |6336 |19 | ticket | 0 | 0 |10 | EYP Prospective Clients | 0 | 0 |0 | 0 | 0 | new| 0 | | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2002-09-20 12:31:02 | 1970-01-01 00:00:00 | 31122 | 2002-09-20 12:31:09 | 31122 | 2002-09-20 12:31:02 | 0 6341 |6341 |19 | ticket | 0 | 0 |10 | IP Prospective Clients | 0 | 0 |0 | 0 | 0 | new| 0 | | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2002-09-20 14:34:25 | 1970-01-01 00:00:00 | 31122 | 2002-09-20 14:34:26 | 31122 | 2002-09-20 14:34:25 | 0(4 rows) Time: 900.930 ms With The explain analyze below: rt3=# explain analyze SELECT DISTINCT main.* FROM Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId)) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId)) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id)) WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( ( ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') ) ) AND ( (main.Status = 'new')OR(main.Status = 'open') ) ) ORDER BY main.Priority DESC LIMIT 10; QUERY
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with
On Thu, 30 Oct 2003 [EMAIL PROTECTED] wrote:
> >> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also.
> >> But the
> >> question is my does PostgreSQL suffer so badly ?? I think not all developers
> >> write very nice
> >> SQLs.
> >>
> >> Its really sad to see that a fine peice of work (RT) is performing sub-optimal
> >> becoz of
> >> malformed SQLs. [ specially on database of my choice ;-) ]
> >
> > Post EXPLAIN ANALYZES of the queries you're running, then maybe you'll be able to
> > get some
> > useful help from this list. Until then, it's very hard to speculate as to why
> > PostgreSQL is
> > slower. -sc
>
> Here It is:
>
> in case they are illegeble please lemme know i will attach it as .txt
> files.
>
> Slower One:
>
> explain analyze SELECT DISTINCT main.* FROM Groups main , Principals Principals_1,
> ACL ACL_2
> WHERE ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( (
> ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND (
> main.Domain =
> 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence')
> AND main.id =
> Principals_1.id) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR
> ( main.Domain
> = 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type =
> ACL_2.PrincipalType AND main.id
> = Principals_1.id) ) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType =
> 'RT::Queue' AND
> ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC ;
Note here:
Merge Join
(cost=1788.68..4735.71 rows=1 width=85)
(actual time=597.540..1340.526 rows=20153 loops=1)
Merge Cond: ("outer".id = "inner".id)
This estimate is WAY off. Are both of those fields indexed and analyzed?
Have you tried upping the statistics target on those two fields?
I assume they are compatible types.
You might try 'set enable_mergejoin = false' and see if it does something
faster here. Just a guess.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
> On Thu, 30 Oct 2003 [EMAIL PROTECTED] wrote:
>
>> >> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also.
>> >> But the
>> >> question is my does PostgreSQL suffer so badly ?? I think not all developers
>> >> write very
>> >> nice SQLs.
>> >>
>> >> Its really sad to see that a fine peice of work (RT) is performing sub-optimal
>> >> becoz of
>> >> malformed SQLs. [ specially on database of my choice ;-) ]
>> >
>> > Post EXPLAIN ANALYZES of the queries you're running, then maybe you'll be able to
>> > get some
>> > useful help from this list. Until then, it's very hard to speculate as to why
>> > PostgreSQL is
>> > slower. -sc
>>
>> Here It is:
>>
>> in case they are illegeble please lemme know i will attach it as .txt files.
>>
>> Slower One:
>>
>> explain analyze SELECT DISTINCT main.* FROM Groups main , Principals Principals_1,
>> ACL ACL_2
>> WHERE ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( (
>> ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND (
>> main.Domain =
>> 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence')
>> AND main.id
>> = Principals_1.id) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25)
>> OR (
>> main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type =
>> ACL_2.PrincipalType AND main.id = Principals_1.id) ) AND (ACL_2.ObjectType =
>> 'RT::System' OR
>> (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC ;
>
> Note here:
>
> Merge Join
> (cost=1788.68..4735.71 rows=1 width=85)
> (actual time=597.540..1340.526 rows=20153 loops=1)
> Merge Cond: ("outer".id = "inner".id)
>
> This estimate is WAY off. Are both of those fields indexed and analyzed?
Yes both are primary keys. and i did vacuum full verbose analyze;
Have you tried
> upping the statistics target on those two fields?
> I assume they are compatible types.
Yes they are
>
> You might try 'set enable_mergejoin = false' and see if it does something faster
> here. Just a
> guess.
Did not help
regds
mallah.
-
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] analyzing postgresql performance for dbt-2
I've done a better controlled series of tests where I restore the database before each test and have grabbed sar and oprofile data: http://developer.osdl.org/markw/dbt2-pgsql/176/ - load of 100 warehouses - metric 1234.52 http://developer.osdl.org/markw/dbt2-pgsql/177/ - load of 120 warehouses - metric 1259.43 http://developer.osdl.org/markw/dbt2-pgsql/178/ - load of 140 warehouses - metric 1244.33 For the most part our primary metric, and the vmstat and sar output look fairly close for each run. Here are a couple of things that I've found to be considerably different from run 176 to 178: - oprofile says postgresql calls to SearchCatCache increased ~ 20% - readprofile says there are 50% more calls in the linux kernel to do_signaction (in kernel/signal.c) Would these two things offer any insight to what might be throttling the throughput? Thanks, Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
<[EMAIL PROTECTED]> writes: > Actually PostgreSQL is at par with MySQL when the query is being > Properly Written(simplified) These are not the same query, though. Your original looks like SELECT DISTINCT main.* FROM Groups main , Principals Principals_1, ACL ACL_2 WHERE ((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser')) AND ((ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND (main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') AND main.id = Principals_1.id) OR (((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR (main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND main.Type = ACL_2.PrincipalType AND main.id = Principals_1.id)) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25)) ORDER BY main.Name ASC where the replacement is SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 using(id) join ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) WHERE ((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser')) AND ((ACL_2.PrincipalType = 'Group' AND (main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence')) OR (((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR (main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND main.Type = ACL_2.PrincipalType)) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25)) ORDER BY main.Name ASC ; You have made the condition "ACL_2.PrincipalId = Principals_1.id" required for all cases, where before it appeared in only one arm of an OR condition. If the second query is correct, then the first one is wrong, and your real problem is that your SQL generator is broken. (I'd argue that the SQL generator is broken anyway ;-) if it generates such horrible conditions as that. Or maybe the real problem is that the database schema is a mess and needs rethinking.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] vacuum locking
Greg Stark writes: > > > SELECT a, (SELECT name FROM t2 WHERE t2.f2 = t1.f2) > > > FROM t1 > > > GROUP BY f2 > > > > This doesn't solve the problem. It's the GROUP BY that is doing the > > wrong thing. It's grouping, then aggregating. > > But at least in the form above it will consider using an index on f2, and it > will consider using indexes on t1 and t2 to do the join. There are 20 rows in t2, so an index actually slows down the join. I had to drop the index on t1.f2, because it was trying to use it instead of simply sorting 20 rows. I've got preliminary results for a number of "hard" queries between oracle and postgres (seconds): PG ORA 0 5 q1 1 0 q2 0 5 q3 2 1 q4 219 7 q5 217 5 q6 79 2 q7 31 1 q8 These are averages of 10 runs of each query. I didn't optimize pctfree, etc., but I did run analyze after the oracle import. One of the reason postgres is faster on the q1-4 is that postgres supports OFFSET/LIMIT, and oracle doesn't. q7 and q8 are the queries that I've referred to recently (avg of group by). q5 and q6 are too complex to discuss here, but the fundamental issue is the order in which postgres decides to do things. The choice for me is clear: the developer time trying to figure out how to make the planner do the "obviously right thing" has been too high with postgres. These tests demonstate to me that for even complex queries, oracle wins for our problem. It looks like we'll be migrating to oracle for this project from these preliminary results. It's not just the planner problems. The customer is more familiar with oracle, and the vacuum performance is another problem. Rob ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] vacuum locking
Rob Nagler <[EMAIL PROTECTED]> writes: > q5 and q6 are too complex to discuss here, How do you expect us to get better if you don't show us the problems? BTW, have you tried any of this with a 7.4beta release? Another project that I'm aware of saw several bottlenecks in their Oracle-centric code go away when they tested 7.4 instead of 7.3. For instance, there is hash aggregation capability, which would probably solve the aggregate query problem you were complaining about in http://archives.postgresql.org/pgsql-performance/2003-10/msg00640.php regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
[EMAIL PROTECTED] writes:
> I really not intend to start a flame war here but i am genuinely
> seeking help to retain PostgreSQL as my database for my RT system.
If there are things that can be discovered to feed back to the RT
developers to improve PostgreSQL's usefulness as a data store for RT,
that would be a Good Thing for anyone that would be interested in
using PG+RT.
--
output = reverse("ofni.smrytrebil" "@" "enworbbc")
Re: [PERFORM] vacuum locking
Rob, > q5 and q6 are too complex to discuss here, but the fundamental issue > is the order in which postgres decides to do things. The choice for > me is clear: the developer time trying to figure out how to make the > planner do the "obviously right thing" has been too high with > postgres. These tests demonstate to me that for even complex queries, > oracle wins for our problem. > > It looks like we'll be migrating to oracle for this project from these > preliminary results. It's not just the planner problems. The > customer is more familiar with oracle, and the vacuum performance is > another problem. Hey, we can't win 'em all. If we could, Larry would be circulating his resume'. I hope that you'll stay current with PostgreSQL developments so that you can do a similarly thourough evaluation for your next project. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Postgresql vs OS compatibility matrix
Hi We installed our Postgresql package from the RH CDROM v9. The version is v7.3.2 Is there a compatibility matrix for Postgresql vs OS that I can verify? I have checked the ftp sites for Postgresql software under the binary/RPMS folder and discovered that v7.3.2 is not available for redhat 9.0 Only v7.3.3 and above is available for redhat 9.0 Thank you, REgards. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] vacuum locking
Rob Nagler <[EMAIL PROTECTED]> writes: > One of the reason postgres is faster on the q1-4 is that postgres > supports OFFSET/LIMIT, and oracle doesn't. q7 and q8 are the queries > that I've referred to recently (avg of group by). Well the way to do offset/limit in Oracle is: SELECT * FROM ( SELECT ... , rownum AS n WHERE rownum <= OFFSET+LIMIT ) WHERE n > OFFSET That's basically the same thing Postgres does anyways. It actually has to do the complete query and fetch and discard the records up to the OFFSET and then stop when it hits the LIMIT. > q5 and q6 are too complex to discuss here, but the fundamental issue > is the order in which postgres decides to do things. That true for pretty 99% of all query optimization whether it's on Postgres or Oracle. I'm rather curious to see the query and explain analyze output from q5 and q6. -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Duplicate user in pg_shadow
Hi When I do a SELECT * FROM pg_shadow, I can have more than one user with the same id. This caused the pg_dump to fail. I read that it happened in v7.1.2 and I am currently using v7.3.2 on Redhat v9.0 What can be the causes and how do we rectify it? Thank you, REgards. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Duplicate user in pg_shadow
Maybe you could delete one of the users from the pg_shadow table, do the dump and then after the dump is restored, recreate the dropped user (and it will get a new sysid) Chris [EMAIL PROTECTED] wrote: Hi When I do a SELECT * FROM pg_shadow, I can have more than one user with the same id. This caused the pg_dump to fail. I read that it happened in v7.1.2 and I am currently using v7.3.2 on Redhat v9.0 What can be the causes and how do we rectify it? Thank you, REgards. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
Tom Lane <[EMAIL PROTECTED]> writes: > (I'd argue that the SQL generator is broken anyway ;-) if it generates > such horrible conditions as that. Or maybe the real problem is that > the database schema is a mess and needs rethinking.) I had the same reaction when I first saw those queries. But I think the problem with the RT schema is that it needs to implement an ACL system that satisfies lots of different usage models. Some people that use it want tickets to be accessible implicitly by the opener like a bug tracking system, others want the tickets to be internal only like a network trouble ticketing system. Some people want to restrict specific operations at a fine-grain, others want to be have more sweeping acls. I've tried doing ACL systems before and they always turned into messes long before that point. I always end up pushing back and trying to force the client to make up his or her mind of exactly what he or she needs before my head explodes . If there's a nice general model for ACLs that can include completely different usage models I've never found it. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Duplicate user in pg_shadow
[EMAIL PROTECTED] writes: > When I do a SELECT * FROM pg_shadow, I can have more than one user > with the same id. This caused the pg_dump to fail. > I read that it happened in v7.1.2 and I am currently using v7.3.2 This is *real* hard to believe. Versions 7.2 and later have a unique index on pg_shadow.usesysid. Are you certain the server isn't 7.1 or older? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Duplicate user in pg_shadow
Hi I tried to delete the user from the pg_user but couldnt. This username is being duplicated so we have the same 2 records. What is the cause ? Is it due to memory or wrong configuration? Thank you, REgards. Christopher Kings-Lynne To: [EMAIL PROTECTED] <[EMAIL PROTECTED]cc: [EMAIL PROTECTED] lth.com.au> Subject: Re: [PERFORM] Duplicate user in pg_shadow 30/10/2003 12:24 PM Maybe you could delete one of the users from the pg_shadow table, do the dump and then after the dump is restored, recreate the dropped user (and it will get a new sysid) Chris [EMAIL PROTECTED] wrote: > Hi > > When I do a SELECT * FROM pg_shadow, I can have more than one user with the same id. This caused the pg_dump to > fail. > > I read that it happened in v7.1.2 and I am currently using v7.3.2 on Redhat v9.0 > > What can be the causes and how do we rectify it? > > > > > Thank you, > REgards. > > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Duplicate user in pg_shadow
I tried to delete the user from the pg_user but couldnt. This username is being duplicated so we have the same 2 records. What is the cause ? Is it due to memory or wrong configuration? Maybe it's an index corruption issue. Try reindexing the pg_shadow table, based on the instructions here: http://www.postgresql.org/docs/7.3/static/sql-reindex.html Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Postgresql vs OS compatibility matrix
Oops! [EMAIL PROTECTED] was seen spray-painting on a wall: > We installed our Postgresql package from the RH CDROM v9. > The version is v7.3.2 > > Is there a compatibility matrix for Postgresql vs OS that I can verify? > > I have checked the ftp sites for Postgresql software under the > binary/RPMS folder and discovered that v7.3.2 is not available for > redhat 9.0 Only v7.3.3 and above is available for redhat 9.0 The reason for minor releases is to fix substantial problems. Nobody bothered packaging 7.3.2 for RH9.0 because by the time RH9.0 was available, 7.3.3 or 7.3.4 were available, and there was therefore no point in packaging a version KNOWN TO BE DEFECTIVE when there was a version available KNOWN TO ADDRESS THOSE DEFECTS. Unless you specifically want to live with the defects remedied in 7.3.3 and 7.3.4, then you should upgrade to 7.3.4. It actually appears likely, based on recent discussions, that there will be a 7.3.5; there might be merit in going to that. -- let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;; http://www3.sympatico.ca/cbbrowne/lsf.html "If you pick up a starving dog and make him prosperous, he will not bite you; that is the principal difference between a dog and a man." -- Mark Twain ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Postgresql vs OS compatibility matrix
Chew, First off, this isn't the appropriate list. So if you have follow-up questions, please post them to NOVICE or GENERAL. > I have checked the ftp sites for Postgresql software under the binary/RPMS > folder and discovered that v7.3.2 is not available for redhat 9.0 > Only v7.3.3 and above is available for redhat 9.0 All versions of PostgreSQL from the last 3 years are compatible with RedHat as far as I know. However, 7.3.3 and 7.3.4 are "bug-fix" releases; they fix security problems and a few other known issues. As such, 7.3.2 is not recommended by *anyone* for *any OS*, becuase it has known sercurity, backup, and recovery issue. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
