Re: [SQL] createuser
Craig May writes: > createuser -adq username > > returns > > invalid option adq createuser -a -d -q -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Q: Difference Datatype Polygon - Path
Hi members of [EMAIL PROTECTED], whats the difference between the pg-datatypes polygon and (closed) path? Is there any information available on that topic? yours, sincerely c. hatger -- Dipl.-Ing. C. Hatger Institut für Anwendungen der Geodaesie im Bauwesen (IAGB) Universitaet Stuttgart Geschwister-Scholl-Str. 24/D 70174 Stuttgart mail: [EMAIL PROTECTED] fon: +49-711-121-4055 fax: +49-711-121-4044 url: http://www.uni-stuttgart.de/iagb --
[SQL] createuser
Hi, I'm having trouble with the createuser command. createuser -adq username returns invalid option adq Could someone post an example using these args. Regards, Craig May Enth Dimension http://www.enthdimension.com.au
Re: [SQL] Typecast a user-defined type?
Mark Volpe <[EMAIL PROTECTED]> writes: > someone give me a hint, or point me to the appropriate material on how to get > "value::text" to work with my new type? Write a function "text(yourtype) RETURNS text", and the parser will use it for type coercions from yourtype to text. In general any type coercion can be provided this way --- a function of a single argument, having the same name as its return type, represents a coercion path. regards, tom lane
[SQL] Typecast a user-defined type?
Hi, I've created my own datatype for Postgres. I have found it necessary to be able to convert it to text, so I could match it up with a text column in a UNION. I figured Postgres would do this for me, but then, I'm naive. Can someone give me a hint, or point me to the appropriate material on how to get "value::text" to work with my new type? Thanks, Mark
Re: [SQL] Q: performance on some selects (7.0.2)?
"Emils Klotins" <[EMAIL PROTECTED]> writes: > Sort (cost=171.93..171.93 rows=1 width=56) > -> Nested Loop (cost=0.00..171.92 rows=1 width=56) > -> Nested Loop (cost=0.00..169.95 rows=1 width=36) > -> Seq Scan on articles_groups x (cost=0.00..12.10 > rows=1 width=4) > -> Seq Scan on articles a (cost=0.00..135.55 rows=636 > width=32) > -> Seq Scan on newscategories c (cost=0.00..1.43 rows=43 > width=20) Given the small estimated costs and row counts, I wonder whether you've ever vacuumed articles_groups and newscategories. The plan is not too unreasonable if the planner is right about how many rows will be matched in each of those tables --- but if you've never done a vacuum then the planner has no accurate stats to work with, so its guesses are likely way off. The plan is clearly handicapped by the lack of indexes on article.id and articles_groups.groupid, also. You seem to have indexes on all the wrong columns of articles :-( ... each of those indexes costs you on updates, but will it ever be useful in a query? regards, tom lane
RE: [SQL] Re: OID Perfomance - Object-Relational databases
Title: RE: [SQL] Re: OID Perfomance - Object-Relational databases Given what we've been discussing over the past day or so, can anybody explain this to me: dev=# explain select client.address.postcode from client; NOTICE: QUERY PLAN: Seq Scan on client (cost=0.00..1.01 rows=1 width=4) EXPLAIN dev=# select client.address.postcode from client; ERROR: init_fcache: Cache lookup failed for procedure 18935 Cheers...
[SQL] Re: OID Perfomance - Object-Relational databases
Hi Josh, I didn't get right tho this, because well.. you asked the list, i figured i'd give them a chance first. and they've answered the same as i would have.. > Because it's a very elegant solution to my database structure issues, > I'm using OID's extensively as referents and foriegn keys. However, I > wanted to see if others had previous experience in this (answer as many > as you like): > > 1. Is there a performance loss on searches and joins when I use the OID > as a liniking field as opposed to a SERIAL column? yes, because indexes are automatcally created on serials. > 2. Can I define my own index on the OIDs of a table? sure, create index oid_idx on table(oid); Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose
Re: [SQL] Q: performance on some selects (7.0.2)?
Emils, There is no index on articles.id (should it not be a primary key?) Regards, Grant Emils Klotins wrote: > A typical query runs like this: > > SELECT a.id,a.title,c.fullpath,c.section FROM articles > a,articles_groups x,newscategories c WHERE x.articleid=a.id AND > a.categoryid=c.id AND x.groupid='9590' AND a.status=1 AND > timestamp(a.publishdate,a.publishtime)<'now'::datetime ORDER > BY a.createddate desc,a.createdtime desc LIMIT 3 > > Explain says: > > NOTICE: QUERY PLAN: > > Sort (cost=171.93..171.93 rows=1 width=56) > -> Nested Loop (cost=0.00..171.92 rows=1 width=56) > -> Nested Loop (cost=0.00..169.95 rows=1 width=36) > -> Seq Scan on articles_groups x (cost=0.00..12.10 > rows=1 width=4) > -> Seq Scan on articles a (cost=0.00..135.55 rows=636 > width=32) > -> Seq Scan on newscategories c (cost=0.00..1.43 rows=43 > width=20) > > EXPLAIN > > Now, as I understand the thing that slows everything is the Seq > scan on articles. I wonder why should it be that the query can't use > index? > > TIA! > Emils -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
[SQL] Q: performance on some selects (7.0.2)?
I have the following tables: ** Table "articles" Attribute | Type | Modifier -+-+-- id | integer | title | text| authorid| integer | sourceid| integer | createddate | date| createdtime | time| publishdate | date| publishtime | time| categoryid | integer | groupid | integer | lead| text| body| text| status | integer | typeid | integer | hot | integer | parentid| integer | Indices: articles_categoryid, articles_createddate, articles_createdtime, articles_groupid, articles_hot, articles_publish_datetime, articles_sourceid, articles_status, articles_typeid -- Table "articles_groups" Attribute | Type | Modifier ---+-+-- articleid | integer | not null groupid | integer | not null --- Table "newscategories" Attribute | Type | Modifier ---+--+-- id| integer | not null parentid | integer | not null default '0' name | varchar(255) | not null default '' directoryname | varchar(255) | not null default '' metakeywords | text | status| integer | not null default 1 sortnr| integer | not null default 0 level | integer | not null default 1 fullpath | text | section | integer | Index: newscategories_pkey * A typical query runs like this: SELECT a.id,a.title,c.fullpath,c.section FROM articles a,articles_groups x,newscategories c WHERE x.articleid=a.id AND a.categoryid=c.id AND x.groupid='9590' AND a.status=1 AND timestamp(a.publishdate,a.publishtime)<'now'::datetime ORDER BY a.createddate desc,a.createdtime desc LIMIT 3 Explain says: NOTICE: QUERY PLAN: Sort (cost=171.93..171.93 rows=1 width=56) -> Nested Loop (cost=0.00..171.92 rows=1 width=56) -> Nested Loop (cost=0.00..169.95 rows=1 width=36) -> Seq Scan on articles_groups x (cost=0.00..12.10 rows=1 width=4) -> Seq Scan on articles a (cost=0.00..135.55 rows=636 width=32) -> Seq Scan on newscategories c (cost=0.00..1.43 rows=43 width=20) EXPLAIN Now, as I understand the thing that slows everything is the Seq scan on articles. I wonder why should it be that the query can't use index? TIA! Emils
RE: [SQL] OID Perfomance - Object-Relational databases
Title: RE: [SQL] OID Perfomance - Object-Relational databases I'm a little concerned about all this, because my understanding is that what makes an object database so fast is its ability to directly reference tuples, so that traversing relationships becomes like traversing pointers. The achilles heel of relational databases is the inability to do exactly that. Postgres has a sufficiently extended query language that traversing relationships in this manner can be dealt with, and doing this from a decent OO development tool should remove the general pain of dealing with OIDs. Or is there something that I'm missing? What I expect from an OR database is the speed of an object database (which seems to come mainly from it's OID mechanism), with the manipulative power of a traditional relational database, and then a whole truckload of extras, like rules, procedures, an OR query language, etc. So, effectively, it's more than the sum of ODB and RDB. In order to promote Postgres in the environment that I'm in now, I need it to attain at least the same order of speed as an ODB. Are there any benchmarks anywhere that I can quote against ODBs? I need some help here, because the general feeling that I'm up against is that we should be using either ODB or RDB, not ORDB as it is the worst of both worlds, being slow, not completely object-orientated, and not as flexible as ODBs, and less robust than RDBs, because of the object extensions. I need some ammo. MikeA >> -Original Message- >> From: Josh Berkus [mailto:[EMAIL PROTECTED]] >> Sent: 04 October 2000 05:11 >> To: [EMAIL PROTECTED] >> Cc: [EMAIL PROTECTED] >> Subject: Re: [SQL] OID Perfomance - Object-Relational databases >> >> >> Tom, >> >> > The trouble with pg_dump -o is that after reload, the OID >> > generator >> > will be set to max(any OID in the dumped data). So a >> > dump & reload >> > doesn't do anything to postpone OID-wraparound Ragnarok. >> > >> > As for the likelihood of overflow, figure 4G / tuple >> > creation rate >> > for your installation (not database, but whole >> > installation controlled >> > by one postmaster). Unless your installation has just >> > one active >> > table, per-table sequence values look like a better bet. >> >> Somebody (urgently) needs to tell all of the above to Bruce >> Momjian (I've cc'd him); his book-in-the-making points up >> OID's as a convenient and universal way to identify and link >> tuples (chapter 7) and doen't mention these problems. Who >> can I bug about how useless the above makes OID's? >> >> Thanks for the warning, and thanks Michael for the >> suggestion; I'll use it and send you all back notes on how >> it affects performance. >> >> -Josh >> >> >> >> >>
