Re: [SQL] Case Insensitive searches
I have read the article... tnks, very helpful. But, can I create a index using function like "substring"? I would like to create something like this: CREATE INDEX indtest_01 ON table_01 ((SUBSTRING(month_year, 3, 4) || SUBSTRING(month_year, 1, 2)) 2008/8/4 Terry Lee Tucker <[EMAIL PROTECTED]> > On Monday 04 August 2008 11:09, Frank Bax wrote: > > Terry Lee Tucker wrote: > > > On Monday 04 August 2008 10:05, Richard Broersma wrote: > > >> On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <[EMAIL PROTECTED]> > wrote: > > >>> In some db's if you > > >>> use a lower() or upr() it will always do a table scan instead of > using > > >>> a index > > >> > > >> True, this would also happen in PostgreSQL. However, you can overcome > > >> this by creating a "functional" index: > > >> > > >> > http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html > > >> > > >> This way all expression using where lower( column ) = 'a'. will always > > >> use an index scan. > > > > > > What about using the operator, ~* ? > > > > > > Does that cause a table scan as well? > > > > Whether or not any query uses an index scan or seq scan depends on many > > factors and is not always easily predictable. > > > > Richard's statement about "will always use an index scan" is not > > universally true. If the table is very small; a index scan is NOT used. > > Table statistics could also indicate a seq scan is more efficient > > (suppose 99% of rows had column='a'). > > > > The ~* operator is very likely to scan the entire table because it will > > look for 'A' anywhere in the column (and will therefore match 'Joanne'; > > and I doubt that there is special code to handle case where length of > > argument is exactly the same as column. However; ~* '^a' which anchors > > search to first character is perhaps more likely to use an index scan. > > > > Frank > > Frank, > > Thanks for the response. Actually, from within the applicaion, we use ~* > and > it is anchored with whatever they've typed in the widget as search > criteria. > > Anyway, thanks for the helpful response... > -- > Terry Lee Tucker > Turbo's IT Manager > Turbo, division of Ozburn-Hessey Logistics > 2251 Jesse Jewell Pkwy NE > Gainesville, GA 30501 > Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 > [EMAIL PROTECTED] > www.turbocorp.com > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] Case Insensitive searches
On Wednesday 06 August 2008 07:59, Rafael Domiciano wrote: > I have read the article... tnks, very helpful. > > But, can I create a index using function like "substring"? I would like to > create something like this: Actually, Richard Broersma is the one who commented on that approach. I have never done this but I have read about it. I'm sure it can be done. > > CREATE INDEX indtest_01 ON table_01 > ((SUBSTRING(month_year, 3, 4) || SUBSTRING(month_year, 1, 2)) > > 2008/8/4 Terry Lee Tucker <[EMAIL PROTECTED]> > > > On Monday 04 August 2008 11:09, Frank Bax wrote: > > > Terry Lee Tucker wrote: > > > > On Monday 04 August 2008 10:05, Richard Broersma wrote: > > > >> On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <[EMAIL PROTECTED]> > > > > wrote: > > > >>> In some db's if you > > > >>> use a lower() or upr() it will always do a table scan instead of > > > > using > > > > > >>> a index > > > >> > > > >> True, this would also happen in PostgreSQL. However, you can > > > >> overcome this by creating a "functional" index: > > > > http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html > > > > > >> This way all expression using where lower( column ) = 'a'. will > > > >> always use an index scan. > > > > > > > > What about using the operator, ~* ? > > > > > > > > Does that cause a table scan as well? > > > > > > Whether or not any query uses an index scan or seq scan depends on many > > > factors and is not always easily predictable. > > > > > > Richard's statement about "will always use an index scan" is not > > > universally true. If the table is very small; a index scan is NOT > > > used. Table statistics could also indicate a seq scan is more efficient > > > (suppose 99% of rows had column='a'). > > > > > > The ~* operator is very likely to scan the entire table because it will > > > look for 'A' anywhere in the column (and will therefore match 'Joanne'; > > > and I doubt that there is special code to handle case where length of > > > argument is exactly the same as column. However; ~* '^a' which anchors > > > search to first character is perhaps more likely to use an index scan. > > > > > > Frank > > > > Frank, > > > > Thanks for the response. Actually, from within the applicaion, we use ~* > > and > > it is anchored with whatever they've typed in the widget as search > > criteria. > > > > Anyway, thanks for the helpful response... > > -- > > Terry Lee Tucker > > Turbo's IT Manager > > Turbo, division of Ozburn-Hessey Logistics > > 2251 Jesse Jewell Pkwy NE > > Gainesville, GA 30501 > > Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 > > [EMAIL PROTECTED] > > www.turbocorp.com > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] more than 1000 connections
On Wed, 2008-08-06 at 08:06 +0800, Craig Ringer wrote: > Out of interest - why 1000 connections? > > Do you really expect to have 1000 jobs concurrently active and doing > work? If you don't, then you'll be wasting resources and slowing > things > down for no reason. There is a connection overhead in PostgreSQL - > IIRC > mostly related to database-wide locking and synchronization, but also > some memory for each backend - that means you probably shouldn't run > vastly more backends than you intend to have actively working. > > If you described your problem, perhaps someone could give you a useful > answer. Your mention of pgpool suggests that you're probably using a > web > app and running into connection count limits, but I shouldn't have to > guess that. > > -- > Craig Ringer This is actually a fantastic point. Have you considered using more than one box to field the connections and using some sort of replication or worker process to move them to a master database of some sort? I don't know about the feasibility of it, but it might work out depending on what kind of application you're trying to write. Disclaimer: I work in a data warehousing and we only have 45 concurrent connections right now. OLTP and/or large connection counts isn't really what I spend my days thinking about. ;-) -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] more than 1000 connections
On Wed, Aug 6, 2008 at 11:54 AM, Mark Roberts <[EMAIL PROTECTED]> wrote: > > On Wed, 2008-08-06 at 08:06 +0800, Craig Ringer wrote: >> Out of interest - why 1000 connections? >> >> Do you really expect to have 1000 jobs concurrently active and doing >> work? If you don't, then you'll be wasting resources and slowing >> things >> down for no reason. There is a connection overhead in PostgreSQL - >> IIRC >> mostly related to database-wide locking and synchronization, but also >> some memory for each backend - that means you probably shouldn't run >> vastly more backends than you intend to have actively working. >> >> If you described your problem, perhaps someone could give you a useful >> answer. Your mention of pgpool suggests that you're probably using a >> web >> app and running into connection count limits, but I shouldn't have to >> guess that. >> >> -- >> Craig Ringer > > This is actually a fantastic point. Have you considered using more than > one box to field the connections and using some sort of replication or > worker process to move them to a master database of some sort? I don't > know about the feasibility of it, but it might work out depending on > what kind of application you're trying to write. > > Disclaimer: I work in a data warehousing and we only have 45 concurrent > connections right now. OLTP and/or large connection counts isn't really > what I spend my days thinking about. ;-) > > -Mark > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > I have many trouble's with server, because my webmail(roundcube) works with the db and the machine only have 2G of RAM but collapse with 60 concurrent connections, I try with persistent connections and the same problem, I need configure a pool of connection or something. my config max_connections = 100; shared_buffer = 32MB increase to 460 connections and 128MB of shared buffers but it's the same -- Jorge Andrés Medina Oliva. Evolve or die! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] more than 1000 connections
On Wed, Aug 6, 2008 at 10:42 AM, Jorge Medina <[EMAIL PROTECTED]> wrote: >> > I have many trouble's with server, because my webmail(roundcube) works > with the db and the machine only have 2G of RAM but collapse with 60 > concurrent connections, I try with persistent connections and the same > problem, I need configure a pool of connection or something. > my config > max_connections = 100; > shared_buffer = 32MB > increase to 460 connections and 128MB of shared buffers but it's the same What, exactly, are the symptoms of a collapse? What do the logs (pgsql, system, your application) have to say? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to creat tables using record ID in for loop
1. I have created the first table and inserted records as below
CREATE TABLE mytest (
staid varchar(20),
kdesc varchar(50) -- description of gage station
)
WITHOUT OIDS;
INSERT INTO mytest VALUES ('96784002', 'mylocation #1');
INSERT INTO mytest VALUES ('02385067', 'mylocation #2');
INSERT INTO mytest VALUES ('01734056', 'mylocation #3');
INSERT INTO mytest VALUES ('04784097', 'mylocation #4');
INSERT INTO mytest VALUES ('16784201', 'mylocation #5');
2. The records look like this.
SELECT * FROM mytest;
++
"96784002";"mylocation #1"
"02385067";"mylocation #2"
"01734056";"mylocation #3"
"04784097";"mylocation #4"
"16784201";"mylocation #5"
++
3. What I want to do is to create tables with staid from mytest table using for
loop.But, since the name of tables should be in text format, I added a string
's' to staid.
Thus, the name of table should be like this s96784002 because of 's' +'staid'.
+++Example+++
CREATE TABLE s06784000 (
staid varchar(50),
valreal, -- streamflow
datedate)
WITHOUT OIDS;
4. I must create tables using for loop because I have millions of records.
I appreciate your help in advance.
Jae
_
강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! 지금 로그인해 보세요!
http://www.hotmail.com
Re: [SQL] more than 1000 connections
On Wed, Aug 6, 2008 at 1:38 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Wed, Aug 6, 2008 at 11:29 AM, Jorge Medina <[EMAIL PROTECTED]> wrote: >> On Wed, Aug 6, 2008 at 12:47 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: >>> On Wed, Aug 6, 2008 at 10:42 AM, Jorge Medina <[EMAIL PROTECTED]> wrote: > I have many trouble's with server, because my webmail(roundcube) works with the db and the machine only have 2G of RAM but collapse with 60 concurrent connections, I try with persistent connections and the same problem, I need configure a pool of connection or something. my config max_connections = 100; shared_buffer = 32MB increase to 460 connections and 128MB of shared buffers but it's the same >>> >>> What, exactly, are the symptoms of a collapse? What do the logs >>> (pgsql, system, your application) have to say? >>> >> affect directly the performance > > I'm trying to help you here, but that answer helps no one. > I know, sorry but the logs don't show anything when many people try login from the webmail begin to grow connections to postgresql and the all system turn too slow. -- Jorge Andrés Medina Oliva. Evolve or die! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] more than 1000 connections
On Wed, Aug 6, 2008 at 1:34 PM, Jorge Medina <[EMAIL PROTECTED]> wrote: > On Wed, Aug 6, 2008 at 1:38 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: >> On Wed, Aug 6, 2008 at 11:29 AM, Jorge Medina <[EMAIL PROTECTED]> wrote: >>> On Wed, Aug 6, 2008 at 12:47 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: On Wed, Aug 6, 2008 at 10:42 AM, Jorge Medina <[EMAIL PROTECTED]> wrote: >> > I have many trouble's with server, because my webmail(roundcube) works > with the db and the machine only have 2G of RAM but collapse with 60 > concurrent connections, I try with persistent connections and the same > problem, I need configure a pool of connection or something. > my config > max_connections = 100; > shared_buffer = 32MB > increase to 460 connections and 128MB of shared buffers but it's the same What, exactly, are the symptoms of a collapse? What do the logs (pgsql, system, your application) have to say? >>> affect directly the performance >> >> I'm trying to help you here, but that answer helps no one. >> > I know, sorry but the logs don't show anything when many people try > login from the webmail begin to grow connections to postgresql and the > all system turn too slow. I'm not trying to be difficult, but there's a huge difference between the system slowing down a bit, slowing down a lot, slowing down to a crawl, and actually collapsing (failing to respond.) Are you running out of available connections? Do you have a lot that are idle? Do you run the machine out of memory? What are the symptoms of your failure? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Single Quote in tsquery
Ryan Wallace wrote: > I am trying to perform a full text search for the word 'ksan (which > starts with a quote). After much frustration and syntax errors I > stumbled upon the following statement which seems to work: > > select * > from items > where to_tsvector(name) @@ to_tsquery(E'[\']ksan') > > I would like to know if this is actually the correct way to search for > this word? The use of brackets isn't documented anywhere that I can > find so I'm not sure if it is even doing what I want it to do or if > the correct result is just a coincidence. I think the tsearch grammar supports something it calls "simplified regular expressions" or some such, which includes character classes (delimited by brackets). So it would seem that what you show is actually supported and correct. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to creat tables using record ID in for loop
On mið, 2008-08-06 at 18:52 +, CHUNRIMACHUNRIMA wrote: > "96784002";"mylocation #1" > "02385067";"mylocation #2" > "01734056";"mylocation #3" ... > 3. What I want to do is to create tables with staid from mytest table > using for loop. ... > +++Example+++ > > CREATE TABLE s06784000 ( > staid varchar(50), > valreal, -- streamflow > datedate > ) > > WITHOUT OIDS; what about a query that generates texts like 'CREATE TABLE s06784000 .;' for each row of your table? then you can either feed the output to psql, or just EXECUTE them in a PL/pgSQL function. > > 4. I must create tables using for loop because I have millions of > records. you want to create millions of tables? you should do a bit of performance testing before you commit to this, as I imagine that you might get into scalability problems. gnari -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] more than 1000 connections
Jorge Medina wrote: > I have many trouble's with server, because my webmail(roundcube) works > with the db and the machine only have 2G of RAM 2GB of RAM can go a long way. It looks like this webmail app is sensible enough to use IMAP for mail store access, so you don't have to worry about messages in the database. That should give you quite a bit of working memory for other things. It would help to know how large your database is - both in physical on-disk size, and how large an SQL dump (with pg_dump) of the database is. > but collapse with 60 > concurrent connections, I try with persistent connections and the same > problem First: PHP's persistent database connections are a *bad* answer. They handle variable spiky very poorly, and they're wasteful of database server resources. Look into using a connection pooler like pgpool. As for "collapse"... that's completely uninformative. - Describe the symptoms of a collapse from the user's perspective. What stops working, or slows down? How? When? Does it recover? - Describe the symptoms of a collapse from the admin's perspective. How does the server behave? What is in the error logs? What do the usual performance monitoring tools tell you? Start with: - Does the server run low on memory? Does it have too many processes struggling for time on too few CPUs? Is it stalling due to disk I/O bottlenecks? - In `free -m' do you see increasing swap usage? - If you follow `vmstat 1' output, do you see increasing swap activity as load increases? Does disk throughput increase or decrease when the server "collapses"? What is CPU activity like when the server "collapses"? - In `top', what processes are keeping the server's CPUs busy? Are any particular processes hogging CPU time when the server "collapses"? - In `top', what are the states of the apache and postgres processes of interest? If you see lots of processes in the D state then you might well have disk I/O bandwidth or scheduling issues. - If you use `tail -f' to follow BOTH the apache and postgresql logs (in different terminals), do you see anything change, any warnings/errors, etc as the server "collapses"? - Does the server recover from "collapse" when load is reduced? - Can you still ping the server when it has "collapsed"? - Can you still ssh into the server when it has "collapsed"? Use `ssh -v' so you can see the progress of the connection. Also, please provide a spec for your server hardware, including: - CPU type and number of CPUs/cores - Operating system/distro and version - PostgreSQL version - Where you got PostgreSQL from/how you installed it - Model of RAID controller you use and its configuration eg RAID 5, RAID 10, etc. - Disk info: -- Number of disks -- Interface of disk(s) eg SATA, PATA, SAS, SCSI -- Capacity of disk(s) -- Spindle speed of disk(s), eg 7200RPM, 10kRPM, 15kRPM Finally: - We know the physical RAM is 2GB, but how much RAM is free when the server is idle? Use `free -m' and read the value for free memory on the +-buffers/cache line. [I'm going to put together a "so, you think we're psychic" email template on the wiki soon, as the number of people who don't provide the basic information needed to usefully answer most questions seems to be continually increasing.] > I need configure a pool of connection or something. Yes, I'd say so. What is preventing you from trying that? What is your question? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to creat tables using record ID in for loop
The function to treate tables is: CREATE OR REPLACE FUNCTION cr_tbls_by_staid() RETURNS INTEGER AS $$ DECLARE stid INTEGER; q TEXT; BEGIN FOR stid IN SELECT staid FROM mytest LOOP q:= 'CREATE TABLE "s' || staid || '" (staid varchar(50), val real, dt date);' RAISE NOTICE 'query is: %', q; --for debug EXECUTE q; END LOOP; RETURN 1; END; $$ LANUAGE plpgsql; However, there are some advices with regards to your question. First, as Rangar noted, you could fall into scalability issue when create a couple of millions of tables. You probably should to examine data design for your DB. For example, you could create the only table like this: CREATE TABLE sta_descs (staid varchar(50), val real, dt date) WITHOUT OIDS; and store there all records you want. Such a table could be easily joined with mytest by staid. Obviosly, sta_desct.staid have to be indexed. Second, take a look at SQL syntax to figure it out how the tables and fields could be named: http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS PS. I didn't test is function works properly as I have no access to PgDB right now. HTH -- Best regards. Yuri. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
