[SQL] Re: Query optimizing - paradox behave
Hallo! Thanks a lot to You Tom. I stared only at the user and the system time. I didn't found any C-function, with wich I could measure the whole time, so I used getrusage(). I did not recognize, that the whole time is reduced, because the User time is increased. Could that anybody explain to me? Why is the usertime increased and the whole time is decreased? # Anyway .. Thanks all a lot for Your effort. I will now tune my radiology-database further... Thankful Greetings David "David M. Richter" <[EMAIL PROTECTED]> writes: > The query with the 3 tables is faster than the query with 2 tables. How you figure that? > time psql -d compare -c "SELECT patient.*,study.* FROM > patient,study,relpatient_study000 r0 WHERE > (patient.chiliOID=r0.parentOID AND study.chiliOID=r0.childOID) order by > patient.name using <" > 3tableni > 1.671u 0.130s 0:11.14 16.4% 0+0k 0+0io 208pf+0w > time psql -d pacs -c "SELECT patient.*,study.* FROM patient,study WHERE > (patient.chiliOID=study.patientOID ) order by patient.name using <" > > 2tableni > 1.730u 0.120s 0:09.44 19.5% 0+0k 0+0io 208pf+0w 9.44 vs 11.14 seconds looks like a clear advantage for the second query to me... begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Table scan instead of index scan
Hi, I am sure PostgreSQL is a very powerful product! I use PostgreSQL 7.1.2.4 in two servers under RedHat 7.0. Our database has a table like that: create table tb_client ( id_client varchar(32), client_name varchar(40), bdate date, ... ); create index xiftb_client on tb_client(id_client); When I execute the following query: SELECT * FROM tb_cliente WHERE id_client LIKE '1234%'; One server executes index search and other full table scan. I don't know the reason. If you could help me I would appreciate a lot. Thanks, Mário Sérgio ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Table scan instead of index scan
On Tue, 24 Jul 2001, [ISO-8859-1] Mário Sérgio G Antunes wrote: > I use PostgreSQL 7.1.2.4 in two servers under RedHat 7.0. > Our database has a table like that: > create table tb_client > ( >id_client varchar(32), >client_name varchar(40), >bdate date, > ... > ); > > create index xiftb_client on tb_client(id_client); > > When I execute the following query: > > SELECT * FROM tb_cliente WHERE id_client LIKE '1234%'; > > One server executes index search and other full table scan. > > I don't know the reason. > > If you could help me I would appreciate a lot. There are a few possibilities. Are both servers vacuum analyzed? Are both compiled the same way? Are both running in the same locale and what is it (which affects like optimization)? What is explain's row estimates on the two machines (if one is much larger percentage of the table than the other, that'd also force a table scan) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] how can we get total records in pg server?
hi all, consider the pg server with databases bhuvan uday guru the need is to get TOTAL RECORDS of all the THREE DATABASES or atleast TOTAL RECORDS of ONE DATABASE. Seems to be simple. Thankx in advance and infact i was a newbie. Regards, Bhuvaneswar. "There is nothing new under the sun, but there are lots of old things we don't know yet." -Ambrose Bierce On Mon, 23 Jul 2001, omid omoomi wrote: > you mean this ? > select count(*) from tablefoo; > > > >From: Bhuvan A <[EMAIL PROTECTED]> > >To: [EMAIL PROTECTED] > >Subject: [SQL] how can we get total records in pg server? > >Date: Mon, 23 Jul 2001 20:03:42 +0530 (IST) > > > > > >Hi all, > > > >how can we get the COUNT of total records in the db server? > > > >hope this could be simple for pg experts. > >thankx in advance! > > > >Regards, > >Bhuvaneswar. > > > > Eighty percent of married men cheat in America. The rest cheat in > >Europe. > > -- Jackie Mason > > > > > > > >---(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 > > > _ > Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How to get the server version??
Hi Is there any way, possible an SQL or pgsql command, to get the server version? Thank you Roberto ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: position(text,text) function
Many thanks for all of the assistance on this question.
I can't say I've ever seen the "a in b" syntax in postgres, but
you live and learn. ;-)
Thanks again.
Karl.
In article <[EMAIL PROTECTED]>, Karl Orbell wrote:
> I'm having trouble using the position function, it's a simple built-in function.
>
> It returns the index of a substring within a main string.
>
> But, I just can't get it to work, it always gives this silly parser error.
> I've tried it in a variety of ways with variables instead of constants casting
> to text and using it in other contexts, (ie. in functions, not just in a
> select). What am I doing wrong? Is this not the correct syntax for a two
> argument function?
>
> test=# select position('hello','el');
> ERROR: parser: parse error at or near ","
>
>
> Karl Orbell.
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] How to get the server version??
select version(); >From: Roberto João Lopes Garcia <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Subject: [SQL] How to get the server version?? >Date: Tue, 24 Jul 2001 10:50:50 -0300 > >Hi > >Is there any way, possible an SQL or pgsql command, to get the server >version? > >Thank you > >Roberto > > >---(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How to get the server version??
From: "Roberto João Lopes Garcia" <[EMAIL PROTECTED]> > Is there any way, possible an SQL or pgsql command, to get the server version? select version(); - Richard Huxton ---(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: [SQL] how can we get total records in pg server?
Bhuvan, > the need is to get TOTAL RECORDS of all the THREE DATABASES or > atleast > TOTAL RECORDS of ONE DATABASE. I guess that many of us are confused by your question. The total records in a table are easily counted -- just use count(*). However, if your database has many tables, what significance does totalling together all of the records from those different tables have? If I have a reference list with 8 items, do I want to add it to a data table with 300,000 records to make 300,008? No. Further, PostgreSQL does not support multi-database operations of any kind. So if the data in those three databases needs to interact, you should probably combine them into one database. I can't help but feel that there are some basic database concepts you haven't yet acquired. Perhaps you've come to Postgres from Filemaker, and are confused by the difference between "database" and "table". If so, you need to start with some basic education before proceeding on to building a DB application. In english, I would recommend: "PostgreSQL, Introduction and Concepts" by Bruce Momjian, as well as "Database Design For Mere Mortals". If you would prefer another language, poll the list -- someone may be familiar with other texts. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Query optimizing - paradox behave
Hallo Stephan! Are there several versions of dbPG95GetIndex existing, or did you mention postgres version 7.1.2? With a little help I have killed the Problem!! Yeah, But now I trying to improve the C-code. Do You have any experience with optimizing C-Code. Are there some new miracle-like function, wich improves the speed dramatically? Do You know some database options wich I could change for better performance? Thanks a lot for Your suggestions!! David >What version are you using? (dbPG95GetIndex?) begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to get the server version??
On Tue, Jul 24, 2001 at 10:50:50AM -0300, Roberto Jo?o Lopes Garcia wrote: > Hi > > Is there any way, possible an SQL or pgsql command, to get the server version? > template1=# select version(); version --- PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.95.4 (1 row) Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Re: When PostgreSQL compliant JDBC 2.0?
"Ed Yu" <[EMAIL PROTECTED]> writes: > ... I've been trying to get in touch with > whoever works on the postgresql jdbc driver with no luck. You're looking in the wrong mailing list. They hang out in pgsql-jdbc. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] nextval on insert by arbitrary sequence
On Fri, Jul 20, 2001 at 10:13:04AM -0700, Dado Feigenblatt wrote: [...] > You kind of implied the answer to my question. > You just does it in the client instead of using back stored > functions/triggers or contrived SQL to handle that, right? That's correct. -- Henry House OpenPGP key available from http://romana.hajhouse.org/hajhouse.asc PGP signature ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Determining if two subnets intersect
Florian Weimer <[EMAIL PROTECTED]> writes: > Is there some efficient PostgreSQL expression which is true if and > only if two subnets (given as values of type cidr) have non-empty > intersection (even if the intersection is not a CIDR network)? Maybe I'm missing something, but ISTM it's only possible for two CIDR subnets to overlap if one contains the other. So you could check with A <<= B OR B <<= A 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
