Re: [GENERAL] why the need for is null?
Quoting Martijn van Oosterhout <[EMAIL PROTECTED]>: > Annoying, not really. It's actually extremely useful. It's useful having a > value which is never equal to anything else, not even itself. If you use it > to represent "unknown" it will work for you. If you try to use it for > anything else, it will bite you. I need it to represent "empty" because the field in question is a foreign key to another table. If it represented "unknown" the foreign key should block it as it could not possible know if that "unknown" value was valid. But I can't argue against the SQL standard of course. > You could create a new operator, but that means you'll have difficulty > moving it to any database that doesn't have that operator (which is most of > them). Any commercial database vendor would be happy to make such a feature just for that reason: to lock me in to their database :-). I do not try to stay database neutral, and use lots of other features that will only work in postgresql. > If you want it to match perhaps you should forget NULL and use '' (zero > length string) instead. Then I need to have a meaningless entry in the foreign table, and fill my code with special cases that filter out that fake entry before showing the data to the user. Besides who said I didn't want to allow the empty string as valid data? This would be even more an issue if the field was a nummeric, where any nummeric value is ok. If I can not use NULL to represent "empty" or "not applicateable" I would have to make a special field that tells me if I should ignore the previous field or not. Does not sound reasonable when NULL works fine for just that. The best compromise I found so far is this "X=Y or X is null and Y is null" construct. Just looks hard to understand and cumpersome for someone which is not expert on this issue. Baldur This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
On Thu, 1 Jan 2004, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > I take it there is no way of drop'ng OIDs after the fact, eh? :) > > I think we have an ALTER TABLE DROP OIDS command, but it won't instantly > remove the OIDS from the table --- removal happens incrementally as rows > get updated. Maybe that's good enough for your situation though. actually, that would be perfect ... saves having to spend the many many hours to re-index all the URLs, and will at least give a gradual improvement :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > I take it there is no way of drop'ng OIDs after the fact, eh? :) I think we have an ALTER TABLE DROP OIDS command, but it won't instantly remove the OIDS from the table --- removal happens incrementally as rows get updated. Maybe that's good enough for your situation though. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
On Thu, 1 Jan 2004, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > I'm still loading the 'WITHOUT OIDS' database ... should I expect that, > > with CLUSTERing, its performance would be slightly better yet, or would > > the difference be negligible? > > I think the difference will be marginal, but worth doing; you're > reducing the row size from 40 bytes to 36 if I counted correctly, > so circa-10% I/O saving, no? > > 24 bytesminimum 7.4 HeapTupleHeader > 4 bytes OID > 12 bytesthree int4 fields > > On a machine with 8-byte MAXALIGN, this would not help, but on > Intel hardware it should. I take it there is no way of drop'ng OIDs after the fact, eh? :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > I'm still loading the 'WITHOUT OIDS' database ... should I expect that, > with CLUSTERing, its performance would be slightly better yet, or would > the difference be negligible? I think the difference will be marginal, but worth doing; you're reducing the row size from 40 bytes to 36 if I counted correctly, so circa-10% I/O saving, no? 24 bytesminimum 7.4 HeapTupleHeader 4 bytes OID 12 bytesthree int4 fields On a machine with 8-byte MAXALIGN, this would not help, but on Intel hardware it should. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
On Thu, 1 Jan 2004, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > On Thu, 1 Jan 2004, Tom Lane wrote: > >> "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > >>> what sort of impact does CLUSTER have on the system? For instance, an > >>> index happens nightly, so I'm guessing that I'll have to CLUSTER each > >>> right after? > >> > >> Depends; what does the "index" process do --- are ndict8 and friends > >> rebuilt from scratch? > > > nope, but heavily updated ... basically, the indexer looks at url for what > > urls need to be 're-indexed' ... if it does, it removed all words from the > > ndict# tables that belong to that url, and re-adds accordingly ... > > Hmm, but in practice only a small fraction of the pages on the site > change in any given day, no? I'd think the typical nightly run changes > only a small fraction of the entries in the tables, if it is smart > enough not to re-index pages that did not change. > > My guess is that it'd be enough to re-cluster once a week or so. > > But this is pointless speculation until we find out whether clustering > helps enough to make it worth maintaining clustered-ness at all. Did > you get any results yet? Here is post-CLUSTER: QUERY PLAN -- Nested Loop (cost=0.00..19470.40 rows=1952 width=8) (actual time=39.639..4200.376 rows=13415 loops=1) -> Index Scan using n8_word on ndict8 (cost=0.00..70.90 rows=3253 width=8) (actual time=37.047..2802.400 rows=15533 loops=1) Index Cond: (word_id = 417851441) -> Index Scan using url_rec_id on url (cost=0.00..5.95 rows=1 width=4) (actual time=0.061..0.068 rows=1 loops=15533) Index Cond: (url.rec_id = "outer".url_id) Filter: (url ~~ 'http://archives.postgresql.org/%%'::text) Total runtime: 4273.799 ms (7 rows) And ... shit ... just tried a search on 'security invoker', and results back in 2 secs ... 'multi version', 18 secs ... 'mnogosearch', .32sec ... 'mnogosearch performance', 18secs ... this is closer to what I expect from PostgreSQL ... I'm still loading the 'WITHOUT OIDS' database ... should I expect that, with CLUSTERing, its performance would be slightly better yet, or would the difference be negligible? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] why the need for is null?
Tom Lane wrote: Not sure that it's fair to characterize this as a property of the relational model. It is a property of the SQL standard. Yes indeed - I fell into the classic "Relational model and SQL are not the same thing" trap ! Mark ---(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
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
On Thu, 1 Jan 2004, Bruce Momjian wrote: > Marc G. Fournier wrote: > > 186_archives=# \d ndict7 > > Table "public.ndict7" > > Column | Type | Modifiers > > -+-+ > > url_id | integer | not null default 0 > > word_id | integer | not null default 0 > > intag | integer | not null default 0 > > Indexes: > > "n7_url" btree (url_id) > > "n7_word" btree (word_id) > > > > > > The slowdown is the LIKE condition, as the ndict[78] word_id conditions > > return near instantly when run individually, and when I run the 'url/LIKE' > > condition, it takes "forever" ... > > Does it help to CLUSTER url.url? Is your data being loaded in so > identical values used by LIKE are next to each other? I'm loading up a MySQL 4.1 database right now, along side of a PgSQL 7.4 one WITHOUT OIDs ... should take several days to fully load, but it will be interesting to compare them all ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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
Re: [GENERAL] 'like' refuses to use an index???
Joseph Shraibman writes: > Tom Lane wrote: >> BTW, 7.4 also has a specialized index opclass that can be used to >> create LIKE-compatible indexes even if you are using a non-C locale. >> > Where is that documented? http://www.postgresql.org/docs/current/static/indexes-opclass.html > Is it automatic for text fields? No. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Binaries (rpm) for SuSE 9.0...
Andreas writes: > Since it is no official RPM anyway you could build it yourself as well > if your server holds the necesary compilers and whatnot ... > I stopped using SuSE's binaries for PG some time ago since they don't > upgrade officially on newer versions and I'd rather have a "clean" > installation in /usr/local/postgresql than all the stuff directly below > /usr. What would be "official" in your mind? When it's on the next SuSE CD distribution, then it will be official, but until then this is as close as it could get. ---(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
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > 'k, and for todays question ... how does one 'knock up the stats target'? ALTER TABLE [ ONLY ] name [ * ] ALTER [ COLUMN ] column SET STATISTICS integer The default is 10; try 100, or even 1000 (don't think it will let you go higher than 1000). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
On Thu, 1 Jan 2004, Tom Lane wrote:
> Mark Kirkwood <[EMAIL PROTECTED]> writes:
> > Might be worth trying a larger statistics target (say 100), in the hope
> > that the planner then has better information to work with.
>
> I concur with that suggestion. Looking at Marc's problem:
>
> QUERY PLAN
> ---
> Hash Join (cost=13918.23..26550.58 rows=17 width=8) (actual
> time=4053.403..83481.769 rows=13415 loops=1)
>Hash Cond: ("outer".url_id = "inner".rec_id)
>-> Index Scan using n8_word on ndict8 (cost=0.00..12616.09 rows=3219 width=8)
> (actual time=113.645..79163.431 rows=15533 loops=1)
> Index Cond: (word_id = 417851441)
>-> Hash (cost=13913.31..13913.31 rows=1968 width=4) (actual
> time=3920.597..3920.597 rows=0 loops=1)
> -> Seq Scan on url (cost=0.00..13913.31 rows=1968 width=4) (actual
> time=3.837..2377.853 rows=304811 loops=1)
>Filter: ((url || ''::text) ~~
> 'http://archives.postgresql.org/%%'::text)
> Total runtime: 83578.572 ms
> (8 rows)
>
> the slowness is not really in the LIKE, it's in the indexscan on ndict8
> (79 out of 83 seconds spent there). The planner probably would not have
> chosen this plan if it hadn't been off by a factor of 5 on the rows
> estimate. So try knocking up the stats target for ndict8.word_id,
> re-analyze, and see what happens.
'k, and for todays question ... how does one 'knock up the stats target'?
This is stuff I've not played with yet, so a URL to read up on this would
be nice, vs just how to do it?
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
Mark Kirkwood <[EMAIL PROTECTED]> writes:
> Might be worth trying a larger statistics target (say 100), in the hope
> that the planner then has better information to work with.
I concur with that suggestion. Looking at Marc's problem:
QUERY PLAN
---
Hash Join (cost=13918.23..26550.58 rows=17 width=8) (actual time=4053.403..83481.769
rows=13415 loops=1)
Hash Cond: ("outer".url_id = "inner".rec_id)
-> Index Scan using n8_word on ndict8 (cost=0.00..12616.09 rows=3219 width=8)
(actual time=113.645..79163.431 rows=15533 loops=1)
Index Cond: (word_id = 417851441)
-> Hash (cost=13913.31..13913.31 rows=1968 width=4) (actual
time=3920.597..3920.597 rows=0 loops=1)
-> Seq Scan on url (cost=0.00..13913.31 rows=1968 width=4) (actual
time=3.837..2377.853 rows=304811 loops=1)
Filter: ((url || ''::text) ~~ 'http://archives.postgresql.org/%%'::text)
Total runtime: 83578.572 ms
(8 rows)
the slowness is not really in the LIKE, it's in the indexscan on
ndict8 (79 out of 83 seconds spent there). The planner probably would
not have chosen this plan if it hadn't been off by a factor of 5 on the
rows estimate. So try knocking up the stats target for ndict8.word_id,
re-analyze, and see what happens.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Is my MySQL Gaining ?
Tino Wildenhain (Thursday 01 January 2004 11:33) > > I'm just saying that it would be nice to include both CLI and GUI > > interfaces, not to mention things like ODBC, as an alternative to the > > "minimalist" download. > > No. NO! Definitively not. I really dont want GUI interfaces on a > database server. We are not on Windows here where all servers > better reside on the desktop... Obviously, and I did not mean to imply that the standalone distribution should go away. I just think that it would be nice for an all-in-one package to exist, for users just wanting to try out PostgreSQL on their desktop. Of course, if your server didn't have X11 compiled (I don't see why it would), then the graphical components would not (because they could not) be compiled :). Vertu sæll, -- Sigþór Björn Jarðarson (Casey Allen Shobe) http://rivyn.livejournal.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
On Thu, 1 Jan 2004, Arjen van der Meijden wrote: > Marc G. Fournier wrote: > > > > Now, if I knock off the LIKE, so that I'm returning all rows from ndict8, > > join'd to all the URLs that contain them, you get: > > Can't you build seperate databases for each domain you want to index? > Than you wouldn't need the like operator at all. First off, that would make searching across multiple domains difficult, no? Second, the LIKE is still required ... the LIKE allows the search to "group" URLs ... for instance, if I wanted to just search on the docs, the LIKE would look for all URLs that contain: http://www.postgresql.org/docs/%% whereas searching the whole site would be: http://www.postgresql.org/%% > Anyway, that doesn't help you much, perhaps decreasing the size of the > index-tables can help, are they with OIDs ? If so, wouldn't it help to > recreate them without, so you save yourselves 4 bytes per word-document > couple, therefore allowing it to fit in less pages and by that speeding > up the seqscans. This one I hadn't thought about ... for some reason, I thought that WITHOUT OIDs was now the default ... looking at that one now ... > Are _all_ your queries with the like on the url? Wouldn't it help to > create an index on both the wordid and the urlid for ndict8? as mentioned in a previous email, the schema for ndict8 is: 186_archives=# \d ndict8 Table "public.ndict8" Column | Type | Modifiers -+-+ url_id | integer | not null default 0 word_id | integer | not null default 0 intag | integer | not null default 0 Indexes: "n8_url" btree (url_id) "n8_word" btree (word_id) > By the way, can a construction like (tablefield || '') ever use an index > in postgresql? again, as shown in a previous email, the index is being used for the LIKE query ... the big problem as I see it is that the result set from the LIKE is ~20x larger then the result set for the the = ... if there was some way to telling the planner that going the LIKE route was the more expensive of the two (even though table size seems to indicate the other way around), I suspect that that would improve things also ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] speeding up inserts
"Chris Ochs" <[EMAIL PROTECTED]> writes: > Is this a crazy way to handle this? Depends. Do you care if you lose that data (if the system crashes before your daemon can insert it into the database)? I think the majority of the win you are seeing comes from the fact that the data doesn't actually have to get to disk --- your "write to file" never gets further than kernel disk buffers in RAM. I would think that you could get essentially the same win by aggregating your database transactions into bigger ones. From a reliability point of view you're doing that anyway --- whatever work the daemon processes at a time is the real transaction size. regards, tom lane ---(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
Re: [GENERAL] Is my MySQL Gaining ?
Hi Casey, Casey Allen Shobe wrote: Alex Satrapa (Sunday 28 December 2003 22:16) Just convince your distribution's My what? I don't use no stinkin' distribution :). postgresql package maintainer That would be postgresql.org, I know not of binary packages. "suggests/recommends" portion of the package management metadata. Tar does not provide such metadata, and a suggestion is hardly the same as an inclusion. I'm just saying that it would be nice to include both CLI and GUI interfaces, not to mention things like ODBC, as an alternative to the "minimalist" download. No. NO! Definitively not. I really dont want GUI interfaces on a database server. We are not on Windows here where all servers better reside on the desktop... But you could provide a wget script for the configure file to fetch all sources one would need to install to his postgres server if desireable. Regards Tino ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] measuring disk usage of records
"Matthew Nuzum" <[EMAIL PROTECTED]> writes: > Thanks for the very quick reply... I didn't know about the dbsize stuff, > they may help. Unfortunately, the records are mixed together. When measuring the on-disk size of a table, don't forget to include its indexes and its toast table (and the toast table's index). If you are using large objects, you'll also need to think sbout charging for the space occupied by pg_largeobject. regards, tom lane ---(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: [GENERAL] GetLastInsertID ?
On Thu, 2004-01-01 at 15:10, Stephane Pinel wrote: > Happy new year to all pgsql fans ! > > Sorry for this trivial question but I couldn't find an answer in the > archives : > > I use SERIAL type in tables for id columns in order to auto increment > them. Is there a way to get > the last inserted id in the table like we do with MySQL using the > GetLastInsertID ? > > My need is to get back the new id just after inserting. Is nextval can > respond to this need if I call it > just before inserting ? You can use currval() after the insert (specifying DEFAULT for the SERIAL field), or nextval() to get an id to use in the insert. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The LORD shall preserve thy going out and thy coming in from this time forth, and even for evermore." Psalms 121:8 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] GetLastInsertID ?
You should use curval after inserting and this can be done in one step insert into foo (c1,c2,...) values ( v1, v2...); select curval( sequence ); Dave On Thu, 2004-01-01 at 10:10, Stephane Pinel wrote: > Happy new year to all pgsql fans ! > > Sorry for this trivial question but I couldn't find an answer in the > archives : > > I use SERIAL type in tables for id columns in order to auto increment > them. Is there a way to get > the last inserted id in the table like we do with MySQL using the > GetLastInsertID ? > > My need is to get back the new id just after inserting. Is nextval can > respond to this need if I call it > just before inserting ? > > Thank you very much. > > Stéphane > > ---(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 > -- Dave Cramer 519 939 0336 ICQ # 1467551 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] GetLastInsertID ?
If you use nextval before the insert, you should use the value in the insert itself. Afterwards you can use currval to get the just inserted value. I'm surprised you can't find this in the archives, it's asked on an almost weekly basis. It's also question 4.15.2 of the FAQ: http://www.postgresql.org/docs/faqs/FAQ.html#4.15.2 Hope this helps, On Thu, Jan 01, 2004 at 04:10:56PM +0100, Stephane Pinel wrote: > Happy new year to all pgsql fans ! > > Sorry for this trivial question but I couldn't find an answer in the > archives : > > I use SERIAL type in tables for id columns in order to auto increment > them. Is there a way to get > the last inserted id in the table like we do with MySQL using the > GetLastInsertID ? > > My need is to get back the new id just after inserting. Is nextval can > respond to this need if I call it > just before inserting ? > > Thank you very much. > > Stéphane > > ---(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 -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce pgp0.pgp Description: PGP signature
Re: [GENERAL] GetLastInsertID ?
On Thu, 1 Jan 2004, Stephane Pinel wrote: > the last inserted id in the table like we do with MySQL using the > GetLastInsertID ? > > My need is to get back the new id just after inserting. Use currval() after you have inserted. http://www.postgresql.org/docs/current/static/functions-sequence.html -- /Dennis ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Bug and/or feature? Complex data types in tables...
On Dec 31, 2003, at 7:20 PM, Chris Travers wrote: This concept of using complex types in tables actually does have one legitimate use. When used with casts and functions, you could use it as a "poor-man's datatype" development method. Here is a hypothetical example. Imagine for a moment that there was no CIDR datatype. I could create a datatype as a set of ints and then create casting functions which I could use for display of the data. This would be similar to C except that it could be done by people like myself whose C coding skills are not up to the level where I or anyone else would want them in the database backend ;-) This is a situation where PostgreSQL's CREATE DOMAIN, or CREATE TYPE support would be useful, I think. Is there a reason these wouldn't work as well as using a "table type"? Happy New Year! Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] GetLastInsertID ?
Happy new year to all pgsql fans ! Sorry for this trivial question but I couldn't find an answer in the archives : I use SERIAL type in tables for id columns in order to auto increment them. Is there a way to get the last inserted id in the table like we do with MySQL using the GetLastInsertID ? My need is to get back the new id just after inserting. Is nextval can respond to this need if I call it just before inserting ? Thank you very much. Stéphane ---(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: [GENERAL] finding my schema (for sequences)
Let's say I am writing a file for creating a database. Say ...
-- schematest
CREATE SEQUENCE foo_id_seq;
CREATE TABLE foo(
id integer
PRIMARY KEY
DEFAULT nextval('foo_id_seq'),
xx text
);
I want to be able to ...
lee=# drop schema beta cascade;
DROP SCHEMA
lee=# create schema beta;
CREATE SCHEMA
lee=# set search_path to beta;
SET
lee=# \i schematest
CREATE SEQUENCE
CREATE TABLE
Hmm. Just in case it is not clear why I want to do this ... after the
above, if I reconnect and fail to set my search_path, the DEFAULT
fails ...
lee=# insert into beta.foo (xx) values ('one');
ERROR: relation "foo_id_seq" does not exist
I Found the current_schema() function, but my naive attempt ...
CREATE SEQUENCE foo_id_seq;
CREATE TABLE foo(
id integer
PRIMARY KEY
DEFAULT nextval(current_schema()||'.foo_id_seq'),
xx text
);
creates a DEFAULT that looks up the current_schema() at run time
instead of when creating the table. Any hints?
_
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
Marc G. Fournier wrote: Now, if I knock off the LIKE, so that I'm returning all rows from ndict8, join'd to all the URLs that contain them, you get: Can't you build seperate databases for each domain you want to index? Than you wouldn't need the like operator at all. The like-operator doesn't seem to allow a very scalable production environment. And besides that point, I don't really believe a "record per word/document-couple" is very scalable (not in SQL, not anywhere). Anyway, that doesn't help you much, perhaps decreasing the size of the index-tables can help, are they with OIDs ? If so, wouldn't it help to recreate them without, so you save yourselves 4 bytes per word-document couple, therefore allowing it to fit in less pages and by that speeding up the seqscans. Are _all_ your queries with the like on the url? Wouldn't it help to create an index on both the wordid and the urlid for ndict8? Perhaps you can create your own 'host table' (which could be filled using a trigger or a slightly adjusted indexer), and a foreign key from your url table to that, so you can search on url.hostid = X (or a join with that host table) instead of the like that is used now? By the way, can a construction like (tablefield || '') ever use an index in postgresql? Best regards and good luck, Arjen van der Meijden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] What does count(*) count?
Hi Tom, > Another possibility is that t_stockchanges has child table(s). Your > SELECT would count rows in the child tables, but I don't think that That's the case. I tried to copy the content of t_stockchanges table into a temp table. Being very lazy:) I created the temp table using create table... inhetit from ... command instead of creating it independently. I haven't read the manual carefuly enough regarding inherit clause. > pg_dump -t would dump them. No, pg_dump doesn't dump them - this was what I found strange. I suppose this behavior disappears if I drop both table and reload the t_stockchanges from the dump. Thank you All. Bye, -- Csaba ---(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
Re: Mnogosearch (Was: Re: [GENERAL] website doc search is ... )
Might be worth trying a larger statistics target (say 100), in the hope that the planner then has better information to work with. best wishes Mark Marc G. Fournier wrote: he problem is that right now, we look at the LIKE first, giving us ~300k rows, and then search through those for those who have the word matching ... is there some way of reducing the priority of the LIKE part of the query, as far as the planner is concerned, so that it will "resolve" the = first, and then work the LIKE on the resultant set, instead of the other way around? So that the query is only checking 15k records for the 13k that match, instead of searching through 300k? I'm guessing that the reason that the LIKE is taking precidence(sp?) is because the URL table has less rows in it then ndict8? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
