[SQL] Speed or configuration
At our company we are presently using a commercial database that generates results from this query: select xx1,xx2,xx3 from tableX where field1 in (select field1 from tableY where field2=NNN and field3=NNN2 and field4=NNN4) tableX has 790,000 rows, and an index on field1 tableY has abou 175,000 rows and an index that includes field2,field3,field4 (and 2 other fields not being used here) Of course, the order in the indexes is the logical one. I have made copies of the tables from the commercial SQL server to PostgreSQL and PostgreSQL is consistently faster in things like count(*) and certain other queries. But when it comes to the above mentioned query, PostgreSQL simply stays there, forever. The postgres backend must be killed in order to free the client program. Any suggestions? Franz J Fortuny
[SQL] protecting a field after creation
Hello, Is there a way (outside of RULEs and TRIGGERs) to make a field read-only once it is INSERTed or assigned its default value? I'm thinking, for example, of the "created" column that I add to most tables, holding the row's creation timestamp. Thanks in advance, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Murphy's Guide to Science: If it's green or squirms, it's biology. If it stinks, it's chemistry. If it doesn't work, it's physics.
Re: [SQL] protecting a field after creation
Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > Is there a way (outside of RULEs and TRIGGERs) to make a field > read-only once it is INSERTed or assigned its default value? I'm > thinking, for example, of the "created" column that I add to most > tables, holding the row's creation timestamp. An on-update trigger seems like a very simple solution here. You could either copy the old value into the new, or raise an error if they are different, depending on what you want. regards, tom lane
Re: [SQL] Speed or configuration
On Sun, 20 Aug 2000, Franz J Fortuny wrote: > At our company we are presently using a commercial > database that generates results from this query: > > select xx1,xx2,xx3 from tableX > where field1 in > (select field1 from tableY where > field2=NNN and field3=NNN2 and field4=NNN4) > > tableX has 790,000 rows, and an index on field1 > tableY has abou 175,000 rows and an index that includes > field2,field3,field4 (and 2 other fields not being used > here) > > Of course, the order in the indexes is the logical one. > > I have made copies of the tables from the commercial SQL > server to PostgreSQL and PostgreSQL is consistently > faster in things like count(*) and certain other > queries. > > But when it comes to the above mentioned query, > PostgreSQL simply stays there, forever. The postgres > backend must be killed in order to free the client > program. > > Any suggestions? First question(s) I have is what version of PostgreSQL? what hardware is the commercial SQL server running on? PostgreSQL? memory, cpu, hard drives, etc? I run something similar on a 7.0.2 machine with a table consisting of ~9million rows and another of ~100k, and results come back in <1min ... in fact, the exact query I've tried looks something like: SELECT url_id,intag FROM ndict WHERE word_id=-903076670 AND url_id IN ( SELECT distinct url_id FROM ndict WHERE word_id=-70722333 AND url_id IN ( SELECT distinct url_id FROM ndict,url WHERE ndict.word_id=572517542 AND url.rec_id=ndict.url_id AND ((url.url || '') LIKE 'http://www.postgresql.org/%') ) ); with subsequent run results looking like: 0.000u 0.014s 0:24.12 0.0% 768+1116k 0+0io 0pf+0w %time psql -h db -o tmpfile udmsearch < query 0.006u 0.006s 0:08.56 0.0% 0+0k 1+0io 0pf+0w %time psql -h db -o tmpfile udmsearch < query 0.005u 0.000s 0:09.10 0.0% 0+0k 0+0io 0pf+0w %time psql -h db -o tmpfile udmsearch < query 0.000u 0.012s 0:11.07 0.0% 300+540k 0+0io 0pf+0w
Re: [SQL] Speed or configuration
"Franz J Fortuny" <[EMAIL PROTECTED]> writes: > [ this query is slow: ] > select xx1,xx2,xx3 from tableX > where field1 in > (select field1 from tableY where > field2=NNN and field3=NNN2 and field4=NNN4) What version of Postgres are you using, and what does EXPLAIN show as the query plan for this query? How many tableY rows is the sub- query likely to produce, and how many matches do you expect to get from tableX? The Postgres planner is not currently very smart about optimizing sub-SELECTs. We hope to do something about that by 7.2 or so, but in the meantime you should look at transforming the query to some other form. You might find that EXISTS will help: select xx1,xx2,xx3 from tableX where exists (select 1 from tableY where field1=tableX.field1 and field2=NNN and field3=NNN3 and field4=NNN4) However this is unlikely to help much unless you change the index structure for tableY. Perhaps you could write it as a join: select xx1,xx2,xx3 from tableX, tableY where tableX.field1=tableY.field1 and field2=NNN and field3=NNN3 and field4=NNN4 although this won't give quite the same results if there can be multiple matching rows in tableY for a tableX row. (DISTINCT might help if so.) regards, tom lane
Re: [SQL] Speed or configuration
(It won't really be forever, just probably a really long time) You can usually get around it by rewriting the query to use EXISTS rather than IN. Stephan Szabo [EMAIL PROTECTED] On Sun, 20 Aug 2000, Franz J Fortuny wrote: > At our company we are presently using a commercial > database that generates results from this query: > > select xx1,xx2,xx3 from tableX > where field1 in > (select field1 from tableY where > field2=NNN and field3=NNN2 and field4=NNN4) > > tableX has 790,000 rows, and an index on field1 > tableY has abou 175,000 rows and an index that includes > field2,field3,field4 (and 2 other fields not being used > here) > > Of course, the order in the indexes is the logical one. > > I have made copies of the tables from the commercial SQL > server to PostgreSQL and PostgreSQL is consistently > faster in things like count(*) and certain other > queries. > > But when it comes to the above mentioned query, > PostgreSQL simply stays there, forever. The postgres > backend must be killed in order to free the client > program.
[SQL] update syntax error?
Can anybody help with why I am receiving the error
below?
Thanks
K.C.
parts=# \d
av_parts
Table "av_parts"
Attribute |
Type
|
Modifier
-+-+--- itemid
| integer | not null default
nextval('av_parts_itemid_seq'::text) vendorid
| integer |
partnumber |
varchar(25) | alternatepartnumber | varchar(25) |
nsn
| varchar(15) |
description |
varchar(50) |
condition
| varchar(10) |
quantity
| integer |
rawpartnumber | varchar(25) |
rawalternatenumber | varchar(25) |
rawnsnnumber | varchar(15) |
date
| integer |
cagecode
| varchar(10) | Indices:
av_parts_alternatepartnumber_in,
av_parts_itemid_key,
av_parts_nsn_index,
av_parts_partnumber_index,
av_parts_rawalternatenumber_ind,
av_parts_rawnsnnumber_index,
av_parts_rawpartnumber_index,
av_parts_vendorid_index
parts=# UPDATE av_partsparts-# SET rawalternaternumber
= 'NULL' AND alternatepartnumber = 'NULL'parts-# WHERE rawalternatenumber =
'IVA81A';ERROR: left-hand side of AND is type 'unknown', not
'bool'
Re: [SQL] update syntax error?
UPDATE SET field1=,field2=
On Tue, 15 Aug 2000, k.c. hemelstrand wrote:
> Can anybody help with why I am receiving the error below?
>
> Thanks
> K.C.
>
>
> parts=# \d av_parts
> Table "av_parts"
> Attribute |Type | Modifier
>
>
>-+-+---
> itemid | integer | not null default
>nextval('av_parts_itemid_seq'::text)
> vendorid| integer |
> partnumber | varchar(25) |
> alternatepartnumber | varchar(25) |
> nsn | varchar(15) |
> description | varchar(50) |
> condition | varchar(10) |
> quantity| integer |
> rawpartnumber | varchar(25) |
> rawalternatenumber | varchar(25) |
> rawnsnnumber| varchar(15) |
> date| integer |
> cagecode| varchar(10) |
> Indices: av_parts_alternatepartnumber_in,
> av_parts_itemid_key,
> av_parts_nsn_index,
> av_parts_partnumber_index,
> av_parts_rawalternatenumber_ind,
> av_parts_rawnsnnumber_index,
> av_parts_rawpartnumber_index,
> av_parts_vendorid_index
>
>
> parts=# UPDATE av_parts
> parts-# SET rawalternaternumber = 'NULL' AND alternatepartnumber = 'NULL'
> parts-# WHERE rawalternatenumber = 'IVA81A';
> ERROR: left-hand side of AND is type 'unknown', not 'bool'
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
RE: [SQL] Speed or configuration
"What version of Postgres are you using, and what does EXPLAIN show as the query plan for this query? How many tableY rows is the sub- query likely to produce, and how many matches do you expect to get from tableX?" Version: postgresql-7.0.2-2.i386.rpm Explain: Scan table, scan table. (Plus the costs, etc.) About 9,000 matches from tableX are expected, and a lot less from tableY (about 2000, at the most). The index structure of tableY includes 5 fields. Most of the time, we need only the first and second fields. Less often, up to the third field and a lot less often the other 2 left fields (in the "where" clauses). However, field1 of tableX references field1 of tableY. If I use a program to get the results, they come out incredibly fast (faster in postgreSQL than with the commercial program) (A select to get a first record set from tableY, then navigate the rows, executing a select for tableX for each row...) These the structures of the tables: create table tableX ( col0 integer not null primary key, col1 integer not null, col2 integer not null, col3 char(20), col4 char(8), col5 char(8), unique(col1,col2,col3,col4,col5) ); create table tableY ( col0 integer not null references tableX(col0), col1 integer, col2 integer, col3 float, col4 float ); The slow query is: select col1,col2,sum(col3),sum(col4) from tableY where col0 in (select col0 from tableX where col1=:col1 and col2=:col2) group by col1,col2; Explain plan shows ALWAYS scan table, scan table, scan table. tableY contains about 900,000 rows, and tableX about 65,000. Any more light about the slowness? Franz J Fortuny
RE: [SQL] Speed or configuration
"First question(s) I have is what version of PostgreSQL? what hardware is the commercial SQL server running on? PostgreSQL? memory, cpu, hard drives, etc?" Version: postgresql-7.0.2-2.i386.rpm Hardware: Same Hardware for all SQL Servers (same machine, of course, one SQL Server is idle while the other one is active). Memory: 512 MB RAM, 16GB Hard disk. Pentium II (old) I am glad to hear that it's possible to get such good results. I always thought there would be a way. I'll try this query you have suggested... Franz J Fortuny
RE: [SQL] Speed or configuration
On Sun, 20 Aug 2000, Franz J Fortuny wrote: > > > > "What version of Postgres are you using, and what does > EXPLAIN show > as the query plan for this query? How many tableY rows > is the sub- > query likely to produce, and how many matches do you > expect to get > from tableX?" > > Version: postgresql-7.0.2-2.i386.rpm > Explain: Scan table, scan table. (Plus the costs, etc.) use cut-n-paste please, and send us the results of the EXPLAIN ... stuff like the cost estimates and whatnot tell us *so* much ...
Re: [SQL] database design and diagraming book recommendations..
"Francisco Hernandez" <[EMAIL PROTECTED]> > anyone know of a good book or books on database modeling? > like for entity relationship diagrams and such.. To get a good life, go with Chris Date's rather difficult two volume set. -dlj.
[SQL] datatype SET
Does anyone have information on how to create a datatype SET in postgres???
