[SQL] Complex SQL query and performance strategy
Hi, I have a complex SQL query which requires the joining of 18 tables. There are only primary key indices on the table and at the moment it runs a little slow (30s or so) and so I am trying to optimise it. The output of EXPLAIN is a little confusing and seems to vary from run to run. Does the query optimiser have trouble with larger number of table joins? Also this will be running from a web front end, and I hope to have it encapsulated all within a function. Would it be better to break it up into multiple SQL statements/functions? Or to try to really tweak the indices? Thanks for any advice adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Creating Stored Procedures
> I installed 7.3beta3 but how do I do it now? > How do I make a function that returns a dataset > > Greets > Zertox He is an email I received from Joe Conway on the subject a month or so ago > Adam Witney wrote: >> There have been a few emails recently concerning using functions. However I >> am a little confused as to their use with composite types. I can see how to >> return a whole row from a table, but is it possible to return multiple >> fields that do not originate from the same table? > > Sure. But you either need a named composite type that matches the row you want > to return, or you can use a record datatype and specify the column definitions > in the sql statement at run time. > > A composite type exists for each table and view in your database, as well as > any stand-alone composite types you define. So, for example: > > test=# create table foo (f1 int,f2 text); > CREATE TABLE > test=# create table bar (f3 int,f4 text); > CREATE TABLE > test=# create view foobar as select f1,f2,f4 from foo, bar where f1=f3; > CREATE VIEW > test=# insert into foo values(1,'a'); > INSERT 1105496 1 > test=# insert into foo values(2,'b'); > INSERT 1105497 1 > test=# insert into bar values(1,'c'); > INSERT 1105498 1 > test=# insert into bar values(2,'d'); > INSERT 1105499 1 > > -- This uses a named composite type based on the view > test=# create function getfoobar1() returns setof foobar as 'select f1,f2,f4 > from foo, bar where f1=f3' language sql; > CREATE FUNCTION > test=# select * from getfoobar1(); > f1 | f2 | f4 > ++ > 1 | a | c > 2 | b | d > (2 rows) > > -- This uses an anonymous composite type specified at runtime > test=# create function getfoobar2() returns setof record as 'select f1,f2,f4 > from foo, bar where f1=f3' language sql; > CREATE FUNCTION > test=# select * from getfoobar2() as (f1 int,f2 text,f4 text); > f1 | f2 | f4 > ++ > 1 | a | c > 2 | b | d > (2 rows) > > HTH, > > Joe > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL list table names
If you start psql like so psql -E Then all the SQL behind the \d type commands is displayed for you. adam > is it possible to execute an sql query to be able to list the tables's > names? > well, you can do it on psql using \dt. but im talking about the SQL > statement, because i want to execute that query from a script. > > thanks. > > > > alviN > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inherancing
On 7/1/03 11:42 am, "Nasair Junior da Silva" <[EMAIL PROTECTED]> wrote: > Hi people, > supposing i have the tables : > > create table cities (id int, name varchar, primary key(id) ); > create table centers (state varchar(2)) inherits (cities); > > ant the tuple > insert into cities values (1, 'Lajeado'); > > How i do if i want to make this city as a center ? If I understand you correctly you probably want to do this instead... insert into centers values (1, 'Lajeado', 'AZ'); Where AZ is your state HTH adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Inherancing
Yes, if you do insert into cities values (1, 'Lajeado'); insert into centers values (1, 'Lajeado', 'AZ'); Then select * from cities will give you 1 Lajedo 1 Lajedo And select * from centers will give 1 Lajedo AZ However, if rather than duplicating the insert and just doing insert into centers values (1, 'Lajeado', 'AZ'); Then select * from cities will give you 1 Lajedo And select * from centers will give 1 Lajedo AZ Is this what you want? adam > In this case, when i do > select * from cities; > i'll have two tuples with the name 'Lajeado'. > > Does it the only way i have ? > > Thanks again, > > Nasair Júnior da Silva > Lajeado - RS - Brasil. > > Em Tue, 07 Jan 2003 12:02:13 +, Adam Witney <[EMAIL PROTECTED]> > escreveu: >> On 7/1/03 11:42 am, "Nasair Junior da Silva" <[EMAIL PROTECTED]> wrote: >> >>> Hi people, >>> supposing i have the tables : >>> >>> create table cities (id int, name varchar, primary key(id) ); >>> create table centers (state varchar(2)) inherits (cities); >>> >>> ant the tuple >>> insert into cities values (1, 'Lajeado'); >>> >>> How i do if i want to make this city as a center ? >> >> If I understand you correctly you probably want to do this instead... >> >> insert into centers values (1, 'Lajeado', 'AZ'); >> >> Where AZ is your state >> >> HTH >> >> adam >> >> > > > xx===xx > || °v° Nasair Junior da Silva || > || /(_)\ Linux User: 246054 || > || ^ ^ [EMAIL PROTECTED]|| > ||CPD - Desenvolvimento || > ||Univates - Centro Universitário|| > xx===xx > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Is it possible to connect to another database
Take a look at dblink in the contrib directory... This may do what you need adam > Hi, > > I try to find how is it possible to connect 2 databases, with a symbolic > link. > > I have to use tables in another database to test user or other information. > > > Ben > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] query or design question
Hi, I have a table like so CREATE TABLE imagene ( id int bioassay_idint gene_idtext s_row int s_column int s_meta_row int s_meta_column int sig_median numeric bkg_median numeric ); Rows are unique on (bioassay_id, gene_id, s_row, s_column, s_meta_row, s_meta_column) They are grouped like this (note, the counts will not always be the same) cabbage=# select bioassay_id, count(*) from imagene group by bioassay_id; bioassay_id | count -+--- 106 | 10944 107 | 10944 And I need to generate an output like this.. bioassay_id=106bioassay_id=107 -- -- gene_id, sig_median, bkg_median, sig_median, bkg_median I can do something like this SELECT a.gene_id, a.sig_median, a.bkg_median, b.sig_median, b.bkg_median FROM imagene a, imagene b WHERE a.s_meta_row = b.s_meta_row AND a.s_meta_col = b.s_meta_col AND a.s_row = b.s_row AND a.s_column = b.s_column AND a.bioassay_id = 106 AND b.bioassay_id = 107; But this is quite slow... And not generic as I may need to generate the output for more than two bioassay_ids. Also I may need to do an outer join to make sure I get all rows from both data sets? I am not sure if a crosstab function will do this? Any ideas of pointers would be greatly appreciated Thanks Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(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
[SQL] Crosstab question
Hi,
I am trying to figure out the use of crosstab(text sql, int N)
The sql I have is
cabbage=# select geneid, bioassay_id, sig_median from imagene order by 1,2;
geneid | bioassay_id | sig_median
-+-+
16s rRNA (AP1A1)| 107 |65535.0
16s rRNA (AP1A1)| 108 | 1904.0
16s rRNA (AP1A1)| 109 |65535.0
16s rRNA (AP2A1)| 106 | 197.0
16s rRNA (AP2A1)| 108 | 197.0
16s rRNA (AP2A1)| 109 |10525.0
16s rRNA (MWG1B1) | 106 | 49.0
16s rRNA (MWG1B1) | 107 | 282.0
16s rRNA (MWG1B1) | 108 | 49.0
16s rRNA (MWG1B1) | 109 | 282.0
However when I use a crosstab function like so
cabbage=# select * from crosstab('select geneid, bioassay_id, sig_median
from imagene order by 1,2;', 4) as ct(geneid text, b106 numeric, b107
numeric, b108 numeric, b109 numeric);
geneid | b106 | b107 | b108 | b109
-+-+-+-+-
16s rRNA (AP1A1)| 65535.0 | 1904.0 | 65535.0 |
16s rRNA (AP2A1)| 197.0 | 197.0 | 10525.0 |
16s rRNA (MWG1B1) |49.0 | 282.0 |49.0 | 282.0
However it should be:
geneid | b106 | b107 | b108 | b109
-+-+-+-+-
16s rRNA (AP1A1)| | 65535.0 | 1904.0 | 65535.0
16s rRNA (AP2A1)| 197.0 | | 197.0 | 10525.0
16s rRNA (MWG1B1) |49.0 | 282.0 |49.0 | 282.0
The missing values seemed to have been ignored and so the data is being
shifted to the left and so put in the wrong columns.
Am I using this function correctly? What is supposed to happen with missing
values?
Thanks for any help
Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] how to create a multi columns return function ?
Take a look at the section on 'SQL Functions Returning Sets' http://www.postgresql.org/docs/7.3/static/xfunc-sql.html#AEN31304 > Hi > > I'm moving databases from sybase to postgres. > But I have difficulties in creating a postgres equivalent to the sybase stored > procedures... > > Apparently, Postgres functions should work, but the syb stored procedures get > only one parameter and return several colums > > Here's the code I wrote in postgresql : > > create function function_name( int ) returns text > AS ' SELECT column1, column2, column3,...,column15 > FROM table_name > WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 = > \'specific_value2 \' ' > LANGUAGE 'SQL'; > > and I get the message error : returns multi columns > > I'm wondering too if It's possible to create a view with a parameter if > functions don't work. > > Has anybody faced the same problem ? > > I need help > > thanks -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(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
[SQL] Subqueries returning more than one value?
Hi, I am using a function in a subquery, this works ok: SELECT name, (SELECT p_my_func(1)) AS id FROM test; However I would like to have the function return 2 values into the main query... Something like this: SELECT name, (SELECT p_my_func(1)) AS (id, type) FROM test; Of course this gives the error: ERROR: subquery must return only one column Is there any way around this? Thanks for any help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] double left outer join on the same table
On 2/5/04 5:23 pm, "T E Schmitz" <[EMAIL PROTECTED]> wrote: > Hello, > > I have two tables SECTION and BRAND. SECTION is related to BRAND via two > foreign keys. I would like to select ALL SECTIONs whether the FKs are > null or not and fetch the BRAND attributes in one SQL statement. In > other words I need a double outer join. > > Is this possible at all? > > The following doesn't work for two reasons: > a) Table name "brand" specified more than once. > b) how would I specify the same output columns twice? > > SELECT > SECTION.SECTION_PK, > SECTION.SECTION_NAME, > SECTION.BRAND_1_FK, > SECTION.BRAND_2_FK, > BRAND.BRAND_PK, > BRAND.BRAND_NAME > > FROM SECTION > left outer join BRAND on BRAND_PK =BRAND_1_FK > left outer join BRAND on BRAND_PK =BRAND_2_FK > I don't know if this will solve your specific problem, but you can use the same table twice in the same query by using aliases, something like this (untested of course) SELECT SECTION.SECTION_PK, SECTION.SECTION_NAME, SECTION.BRAND_1_FK, SECTION.BRAND_2_FK, a.BRAND_PK, a.BRAND_NAME b.BRAND_PK, b.BRAND_NAME FROM SECTION left outer join BRAND a on BRAND_PK =BRAND_1_FK left outer join BRAND b on BRAND_PK =BRAND_2_FK -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] pgdump by schema?
From: pg_dump --help -n, --schema=SCHEMA dump the named schema only > Is there a way to dump everything in a particular schema? > > > Bradley Miller > NUVIO CORPORATION > Phone: 816-444-4422 ext. 6757 > Fax: 913-498-1810 > http://www.nuvio.com > [EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] which is better: using OR clauses or UNION?
Hi,
I have a query hitting a table of 25 million rows. The table has a text field
('identifier') which i need to query for matching rows. The question is if i
have multiple strings to match against this field I can use multiple OR
sub-statements or multiple statements in a UNION. The UNION seems to run
quicker is this to be expected? or is there anything else I can do improve
the speed of this query? Some query details:
table "dba_data_base", index:
"in_dba_data_base_identifier" btree (lower(identifier) varchar_pattern_ops)
Query 1
---
datadb=#explain analyse SELECT a.bioassay_id, a.identifier, ratio, log_ratio,
p_value
FROM
dba_data_base a
WHERE
( lower(identifier) LIKE lower('BUGS001884677') OR
lower(identifier) LIKE lower('BUGS001884678') OR
lower(identifier) LIKE lower('BUGS001884679') OR
lower(identifier) LIKE lower('SpTIGR4-2210 (6F24)') )
ORDER BY a.identifier;
QUERY PLAN
Sort (cost=14930.13..14939.77 rows=3857 width=62) (actual
time=3208.466..3208.652 rows=318 loops=1)
Sort Key: identifier
-> Bitmap Heap Scan on dba_data_base a (cost=134.43..14700.38 rows=3857
width=62) (actual time=81.106..3207.721 rows=318 loops=1)
Recheck Cond: ((lower(identifier) ~~ 'bugs001884677'::text) OR
(lower(identifier) ~~ 'bugs001884678'::text) OR (lower(identifier) ~~
'bugs001884679'::text) OR (lower(identifier) ~
~ 'sptigr4-2210 (6f24)'::text))
Filter: ((lower(identifier) ~~ 'bugs001884677'::text) OR
(lower(identifier) ~~ 'bugs001884678'::text) OR (lower(identifier) ~~
'bugs001884679'::text) OR (lower(identifier) ~~ 'spt
igr4-2210 (6f24)'::text))
-> BitmapOr (cost=134.43..134.43 rows=3857 width=0) (actual
time=71.397..71.397 rows=0 loops=1)
-> Bitmap Index Scan on in_dba_data_base_identifier
(cost=0.00..32.64 rows=964 width=0) (actual time=0.029..0.029 rows=0 loops=1)
Index Cond: (lower(identifier) ~=~
'bugs001884677'::character varying)
-> Bitmap Index Scan on in_dba_data_base_identifier
(cost=0.00..32.64 rows=964 width=0) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (lower(identifier) ~=~
'bugs001884678'::character varying)
-> Bitmap Index Scan on in_dba_data_base_identifier
(cost=0.00..32.64 rows=964 width=0) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (lower(identifier) ~=~
'bugs001884679'::character varying)
-> Bitmap Index Scan on in_dba_data_base_identifier
(cost=0.00..32.64 rows=964 width=0) (actual time=71.347..71.347 rows=318
loops=1)
Index Cond: (lower(identifier) ~=~ 'sptigr4-2210
(6f24)'::character varying)
Total runtime: 3208.904 ms
Query 2
---
datadb=#explain analyse SELECT a.bioassay_id, a.identifier, ratio, log_ratio,
p_value
FROM
dba_data_base a
WHERE
lower(identifier) LIKE lower('BUGS001884677')
UNION
SELECT a.bioassay_id, a.identifier, ratio, log_ratio, p_value
FROM
dba_data_base a
WHERE
lower(identifier) LIKE lower('BUGS001884678')
UNION
SELECT a.bioassay_id, a.identifier, ratio, log_ratio, p_value
FROM
dba_data_base a
WHERE
lower(identifier) LIKE lower('BUGS001884679')
UNION
SELECT a.bioassay_id, a.identifier, ratio, log_ratio, p_value
FROM
dba_data_base a
WHERE
lower(identifier) LIKE lower('SpTIGR4-2210 (6F24)')
ORDER BY identifier;
Sort (cost=15702.26..15711.90 rows=3856 width=62) (actual time=3.688..3.886
rows=317 loops=1)
Sort Key: identifier
-> Unique (cost=15414.74..15472.58 rows=3856 width=62) (actual
time=2.663..3.387 rows=317 loops=1)
-> Sort (cost=15414.74..15424.38 rows=3856 width=62) (actual
time=2.660..2.834 rows=318 loops=1)
Sort Key: bioassay_id, identifier, ratio, log_ratio, p_value
-> Append (cost=32.88..15185.06 rows=3856 width=62) (actual
time=0.320..2.131 rows=318 loops=1)
-> Bitmap Heap Scan on dba_data_base a
(cost=32.88..3786.62 rows=964 width=62) (actual time=0.041..0.041 rows=0
loops=1)
Filter: (lower(identifier) ~~
'bugs001884677'::text)
-> Bitmap Index Scan on in_dba_data_base_identifier
(cost=0.00..32.64 rows=964 width=0) (actual time=0.036..0.036 rows=0 loops=1)
Index Cond: (lower(identifier) ~=~
'bugs001884677'::character varying)
