[SQL] Performance issue
I use Apache Web Server and PostgreSQL 7.3 to collect data everyday. Now it has been 5 month since I install that server. I wonder why now my web very slow to retrieve and display data? When I check the memory, I found that postgreSQL client seem not release after allocate. I try to find bug on my script, but everything look clean to me. Anyone have experience like me.. please share info with me Thanks anyway, Ricky Sutanto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Time differences between rows, not columns?
Using postgresSQL 8.0 for windows, running on windows 2003 server 16gb RAM, 3Ghz dual p4. Language: Java - JDBC postgres driver: postgresql-8.0-310.jdbc3.jar I have an application with 3 tables (in this context that is) Table bills bill_id NOT NULL serial ... And other columns Table bill_status bill_id (references bills.bill_id) statusid int4 (references bill_statuslookup.statusid) statustime datetime Table bill_statuslookup statusid serial not null statusname varchar(255) The application basically tracks a workflow of bills flowing fromone department to another. Everytime the bill moves one step, an entry is made into the bill_status table. Eg. Bills table - Bill_id otherfield1 1 Bill_status table: - Bill_id statusidstatustime 1 10 2005-04-04 00:34:31 1 20 2005-04-05 00:55:00 Bill_statuslookup table: - Statusid Statusname 10submitted 20received 30rejected 40accepted .. .. Now my problem is this: 1. Find the time taken for each bill to reach from status 10 to status 20 , given the time of status 10 should be between t1 and t2. Eg I want to know how much time it took for a bill to be accepted after it was submitted (criteria: submitted between yesterday and today) 2. I want to know how many bills took <7 days, how many tok 7-15 days, how many took >15 days etc. The status is a lookup table because the workflow constantly changes, and I can't have submitted_on, recd_on accepted_on times etc in the main bills table as columns because there are way too many statuses in the life of a bill (read invoice). Hope its clear as to what I'm looking for. Any help is greatly appreciated!! Regards, Amit ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Time differences between rows, not columns?
Amit,
You say "I want to know how much time it took for a bill to be
accepted after it was submitted"
So, do you want between 10 and 40, not 10 and 20?
I assume you meant 10 and 40.
Ok, there's a few approaches to your questions, first
To get "how much time it took for a bill to be accepted after
it was submitted":
select bill_id,
(max(case when status_id = 40
then statustime end) -
max(case when status_id = 10
then statustime end))
from bill_status
where status_id in ( 10,40 )
group by bill_id;
that will give you the amt of time (simple date arithmetic, so, in days)
from submission to being accepted.
You also, want a simple distribution, you can use the technique above
But with SUM:
select sum(case when diff < 7 then 1 else 0 end) as lt_7,
sum(case when diff between 7 and 15 then 1 else 0 end) as
btw_7_15,
sum(case when diff > 15 then 1 else 0 end) as gt_15
from (
select bill_id,
(max(case when status_id = 40
then statustime end) -
max(case when status_id = 10
then statustime end)) as diff
from bill_status
where status_id in ( 10,40 )
group by bill_id
) x;
I have a recipe in my upcoming book ('The SQL Cookbook' by O'Reilly)
So, I'd like to know if what I suggested works out for you.
Obviously, you'll need whatever tweaks to make it perfect for your
system, the feedback I am concerned about is the technique.
hope that helps,
Anthony
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Tuesday, August 30, 2005 5:45 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Time differences between rows, not columns?
Importance: High
Using postgresSQL 8.0 for windows, running on windows 2003 server 16gb
RAM, 3Ghz dual p4.
Language: Java - JDBC postgres driver: postgresql-8.0-310.jdbc3.jar
I have an application with 3 tables (in this context that is)
Table bills
bill_id NOT NULL serial
... And other columns
Table bill_status
bill_id (references bills.bill_id)
statusid int4 (references bill_statuslookup.statusid)
statustime datetime
Table bill_statuslookup
statusid serial not null
statusname varchar(255)
The application basically tracks a workflow of bills flowing fromone
department to another.
Everytime the bill moves one step, an entry is made into the bill_status
table.
Eg.
Bills table
-
Bill_id otherfield1
1
Bill_status table:
-
Bill_id statusidstatustime
1 10 2005-04-04 00:34:31
1 20 2005-04-05 00:55:00
Bill_statuslookup table:
-
Statusid Statusname
10submitted
20received
30rejected
40accepted
..
..
Now my problem is this:
1. Find the time taken for each bill to reach from status 10 to status
20 , given the time of status 10 should be between t1 and t2.
Eg I want to know how much time it took for a bill to be accepted after
it was submitted (criteria: submitted between yesterday and today)
2. I want to know how many bills took <7 days, how many tok 7-15 days,
how many took >15 days etc.
The status is a lookup table because the workflow constantly changes,
and I can't have submitted_on, recd_on accepted_on times etc in the main
bills table as columns because there are way too many statuses in the
life of a bill (read invoice).
Hope its clear as to what I'm looking for.
Any help is greatly appreciated!!
Regards,
Amit
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] REINDEX DATABASE
O Achilleus Mantzios έγραψε στις Jul 28, 2005 : > O Chris Browne έγραψε στις Jul 27, 2005 : > > > I'll try to postpone the next reindexdb at the end of august, > and get some numbers then. > However the big difference in performance as i told was near the 2GB > "threshold", and at *that* point (and maybe for different reasons) > performance gain was remarkable. > Well, today i run reindexdb and i noticed a decrease in database size, from 2890148K to 2527552K, about 12.54% decrease in size. However i have not an estimation of the distribution of the SQL commands (INSERT,UPDATE,DELETE) over August. I am running 7.4.6. > > -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Time differences between rows, not columns?
Thanks!
Amit,You say "I want to know how much time it took for a bill to
be accepted after it was submitted" So, do you want between 10 and 40,
not 10 and 20? I assume you meant 10 and 40...
...Could be any status to any status, I wanted to generalize the concept
for future usage.
I tried the below:
1.
select bill_id,(max(case when statusid = 40 then statustime end) -
max(case when statusid = 10 then statustime end)) from
ap.vits_statusupdate where statusid in ( 10,40 ) group by bill_id;
Took 16 ms...
But meanwhile I also tried:
select a.bill_id, b.statustime-a.statustime from ap.vits_statusupdate a,
ap.vits_statusupdate b where a.statusid = 10 and b.statusid = 40 and
a.bill_id = b.bill_id
Took 15 ms
The second query is taking approximately 1 ms lesser time.. (have tried
about 30 times in pgadmin)
Got about 10 records in my test resultset.
Surprisingly, if I add the order by bill_id cluase at the end of both
queries, then your query performs 1 ms faster than mine, Don't know why,
Is there any performance issues if this is run over 1+ rows?
Which (yours or mine?) do you think will be faster, and more efficient?
2.
select sum(case when diff < 7 then 1 else 0 end) as lt_7,sum(case when
diff between 7 and 15 then 1 else 0 end) as btw_7_15,sum(case when diff
> 15 then 1 else 0 end) as gt_15
from (select bill_id,(max(case when statusid = 40 then statustime end)
- max(case when statusid = 10 then statustime end)) as diff from
ap.vits_statusupdate
where statusid in ( 10,40 ) group by bill_id) x;
Tried this one too with both the queries in the sub-select (yours and
mine), and works perfectly well for me, with accurate answers, and
exactly what I'm looking for! shows 0 ms (??)..
Next question.. Which one should I use? The joined query? Or the max()
query?
Thanks a lot for your time!
Regards,
Amit
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Anthony Molinaro
Sent: Tuesday, August 30, 2005 4:59 PM
To: Wadhwa, Amit; pgsql-sql@postgresql.org
Subject: Re: [SQL] Time differences between rows, not columns?
Amit,
You say "I want to know how much time it took for a bill to be
accepted after it was submitted"
So, do you want between 10 and 40, not 10 and 20?
I assume you meant 10 and 40.
Ok, there's a few approaches to your questions, first
To get "how much time it took for a bill to be accepted after
it was submitted":
select bill_id,
(max(case when status_id = 40
then statustime end) -
max(case when status_id = 10
then statustime end))
from bill_status
where status_id in ( 10,40 )
group by bill_id;
that will give you the amt of time (simple date arithmetic, so, in days)
from submission to being accepted.
You also, want a simple distribution, you can use the technique above
But with SUM:
select sum(case when diff < 7 then 1 else 0 end) as lt_7,
sum(case when diff between 7 and 15 then 1 else 0 end) as
btw_7_15,
sum(case when diff > 15 then 1 else 0 end) as gt_15
from (
select bill_id,
(max(case when status_id = 40
then statustime end) -
max(case when status_id = 10
then statustime end)) as diff
from bill_status
where status_id in ( 10,40 )
group by bill_id
) x;
I have a recipe in my upcoming book ('The SQL Cookbook' by O'Reilly)
So, I'd like to know if what I suggested works out for you.
Obviously, you'll need whatever tweaks to make it perfect for your
system, the feedback I am concerned about is the technique.
hope that helps,
Anthony
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Tuesday, August 30, 2005 5:45 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Time differences between rows, not columns?
Importance: High
Using postgresSQL 8.0 for windows, running on windows 2003 server 16gb
RAM, 3Ghz dual p4.
Language: Java - JDBC postgres driver: postgresql-8.0-310.jdbc3.jar
I have an application with 3 tables (in this context that is)
Table bills
bill_id NOT NULL serial
... And other columns
Table bill_status
bill_id (references bills.bill_id)
statusid int4 (references bill_statuslookup.statusid)
statustime datetime
Table bill_statuslookup
statusid serial not null
statusname varchar(255)
The application basically tracks a workflow of bills flowing fromone
department to another.
Everytime the bill moves one step, an entry is made into the bill_status
table.
Eg.
Bills table
-
Bill_id otherfield1
1
Bill_status table:
-
Bill_id statusidstatustime
1 10 2005-04-04 00:34:31
1 20 2005-04-05 00:55:00
Bill_statuslookup table:
-
Statusid Statusname
10submitted
20received
30rejected
40accepted
..
Re: [SQL] Performance issue
On Tue, Aug 30, 2005 at 03:42:06PM +0700, Ricky Sutanto wrote: > I use Apache Web Server and PostgreSQL 7.3 to collect data everyday. Now it > has been 5 month since I install that server. Which release of PostgreSQL 7.3? What operating system and version? > I wonder why now my web very slow to retrieve and display data? Are only web-based applications slow, or are the same queries issued from another client (e.g., psql) also slow? Are you running VACUUM ANALYZE on a regular basis? If not then you might have a lot of dead tuples in your database. In 7.3 you might also want to REINDEX periodically (this isn't as necessary in later versions). For more information, see "Routine Database Maintenance Tasks" in the documentation: http://www.postgresql.org/docs/7.3/static/maintenance.html If queries are still slow after database maintenance then please post the EXPLAIN ANALYZE output of an example query. Additional info like table descriptions and sizes would also be helpful. > When I check the memory, I found that postgreSQL client seem not release > after allocate. I try to find bug on my script, but everything look clean to > me. What client, and what do you mean by "seem not release after allocate"? What are you doing and how are you checking it? What language are you using? BTW, the pgsql-performance list would be more appropriate for discussing performance issues. Pgsql-sql is supposed to be for SQL-related matters (how to write queries, etc.). -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Time differences between rows, not columns?
Amit,
Glad it worked out :)
As for the performance, lemme say that while I'm a huge fan
Of postgres, my experience in regards to optimization in a production
Environment is limited to Oracle and DB2.
In oracle for example, if you have an index on a numeric field
and perform min/max on it, there's a very cool algorithm
that allows very fast retrieval of that data (basically the index
scan is optimized cuz oracle knows you want only the extreme values).
So, on oracle I'd use the MAX and SUM versions I suggested because
There's a specific optimization to facilitate it and the query, since
It accesses the table only once, is doing less logical reads.
So, at the very least, in the version I suggested, since you are not
Joining, you are performing less logical reads, which is always good.
But, ultimately, you have to test and see what works for you.
The self join might be just fine.
10k rows should not be a problem for either method,
assuming you have an index on statusid and bill_id.
Give it a spin and update this thread.
Regards,
Anthony
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 30, 2005 8:08 AM
To: Anthony Molinaro; pgsql-sql@postgresql.org
Subject: RE: [SQL] Time differences between rows, not columns?
Importance: High
Thanks!
Amit,You say "I want to know how much time it took for a bill to
be accepted after it was submitted" So, do you want between 10 and 40,
not 10 and 20? I assume you meant 10 and 40...
...Could be any status to any status, I wanted to generalize the concept
for future usage.
I tried the below:
1.
select bill_id,(max(case when statusid = 40 then statustime end) -
max(case when statusid = 10 then statustime end)) from
ap.vits_statusupdate where statusid in ( 10,40 ) group by bill_id;
Took 16 ms...
But meanwhile I also tried:
select a.bill_id, b.statustime-a.statustime from ap.vits_statusupdate a,
ap.vits_statusupdate b where a.statusid = 10 and b.statusid = 40 and
a.bill_id = b.bill_id
Took 15 ms
The second query is taking approximately 1 ms lesser time.. (have tried
about 30 times in pgadmin)
Got about 10 records in my test resultset.
Surprisingly, if I add the order by bill_id cluase at the end of both
queries, then your query performs 1 ms faster than mine, Don't know why,
Is there any performance issues if this is run over 1+ rows?
Which (yours or mine?) do you think will be faster, and more efficient?
2.
select sum(case when diff < 7 then 1 else 0 end) as lt_7,sum(case when
diff between 7 and 15 then 1 else 0 end) as btw_7_15,sum(case when diff
> 15 then 1 else 0 end) as gt_15
from (select bill_id,(max(case when statusid = 40 then statustime end)
- max(case when statusid = 10 then statustime end)) as diff from
ap.vits_statusupdate
where statusid in ( 10,40 ) group by bill_id) x;
Tried this one too with both the queries in the sub-select (yours and
mine), and works perfectly well for me, with accurate answers, and
exactly what I'm looking for! shows 0 ms (??)..
Next question.. Which one should I use? The joined query? Or the max()
query?
Thanks a lot for your time!
Regards,
Amit
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Anthony Molinaro
Sent: Tuesday, August 30, 2005 4:59 PM
To: Wadhwa, Amit; pgsql-sql@postgresql.org
Subject: Re: [SQL] Time differences between rows, not columns?
Amit,
You say "I want to know how much time it took for a bill to be
accepted after it was submitted"
So, do you want between 10 and 40, not 10 and 20?
I assume you meant 10 and 40.
Ok, there's a few approaches to your questions, first
To get "how much time it took for a bill to be accepted after
it was submitted":
select bill_id,
(max(case when status_id = 40
then statustime end) -
max(case when status_id = 10
then statustime end))
from bill_status
where status_id in ( 10,40 )
group by bill_id;
that will give you the amt of time (simple date arithmetic, so, in days)
from submission to being accepted.
You also, want a simple distribution, you can use the technique above
But with SUM:
select sum(case when diff < 7 then 1 else 0 end) as lt_7,
sum(case when diff between 7 and 15 then 1 else 0 end) as
btw_7_15,
sum(case when diff > 15 then 1 else 0 end) as gt_15
from (
select bill_id,
(max(case when status_id = 40
then statustime end) -
max(case when status_id = 10
then statustime end)) as diff
from bill_status
where status_id in ( 10,40 )
group by bill_id
) x;
I have a recipe in my upcoming book ('The SQL Cookbook' by O'Reilly)
So, I'd like to know if what I suggested works out for you.
Obviously, you'll need whatever tweaks to make it perfect for your
system, the feedback I am concerned about is the technique.
hope that helps,
Anthony
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Beha
Re: [SQL] Performance issue
On Tue, Aug 30, 2005 at 15:42:06 +0700, Ricky Sutanto <[EMAIL PROTECTED]> wrote: > > I use Apache Web Server and PostgreSQL 7.3 to collect data everyday. Now it > has been 5 month since I install that server. > > I wonder why now my web very slow to retrieve and display data? > When I check the memory, I found that postgreSQL client seem not release > after allocate. I try to find bug on my script, but everything look clean to > me. > > Anyone have experience like me.. please share info with me You haven't given us much to go on. You might have a problem with not properly vacuuming or analyzing or perhaps an issue with index bloat. There are also a number of performance benefits to using more recent versions of postgres. If you want some more specific help you should show us explain analyse resulta for some of your slow queries. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] question
In PHP I use this code with a prepared statement to insert null in the field
justif_emenda when
the value in the form is an empty string or a string with only spaces. I hope
this will help you.
$sql = "INSERT INTO tbl_emenda ("
. "id_emenda, subtipo_emenda, tipo_emenda, "
. "nome_autor, titulo_autor, login_autor, "
. "cod_acao_gov, nome_acao_gov, "
. "texto_emenda, justif_emenda) "
. "VALUES(?,?,?,?,?,?,?,?,?,nullif(trim(' ' from ?),''))";
$stmt = $conn->Prepare($sql);
if (!$conn->Execute($stmt, array(
$id_emenda,
'S',
'Aditiva',
$_SESSION['nome_autor'],
$_SESSION['titulo_autor'],
$_SESSION['login_autor'],
$_REQUEST['selCodAcaoGov'],
$_REQUEST['txtNomeAcaoGov'],
$_REQUEST['txtTexto'],
$_REQUEST['txtJustif']
))) {
erroSQL($conn, $sql);
die("Erro..");
}
___
Yahoo! Acesso Grátis - Internet rápida e grátis.
Instale o discador agora! http://br.acesso.yahoo.com/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] question
On Aug 24, 2005, at 1:05 AM, Matt A. wrote:
We used nullif('$value','') on inserts in mssql. We
moved to postgres and love it but the nullif() doesn't
match empty strings to each other to return null other
than a text type, causing an error. This is a major
part of our application.
I *certainly* hope you're not passing $value in straight from your
web form directly into the SQL. You're opening yourself up for SQL
injection attacks.
Why not just have your app that reads the form generate the proper
value to insert? That is the safe route.
Vivek Khera, Ph.D.
+1-301-869-4449 x806
---(end of broadcast)---
TIP 1: 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] Numerical variables in pqsql statements
Well, browse through this list: http://www.postgresql.org/docs/books/ I can't make any recommendations, as I am fairly familiar with the online documentation, which, when supported by the community, seems to be pretty good. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Aug 29, 2005, at 4:05 PM, Michael Schmidt wrote: Mr. O'Connell, Thanks so much for the insights. Sorry about the basic nature of the question - perhaps a "PostgreSQL for Dummies" book would help me! Michael Schmidt ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] plpgsql question
Can I do something like this: CREATE TABLE sample (id SERIAL, node INTEGER, parent INTEGER); INSERT INTO sample(node,parent) VALUES(1,0); INSERT INTO sample(node,parent) VALUES(2,0); INSERT INTO sample(node,parent) VALUES(3,1); INSERT INTO sample(node,parent) VALUES(4,3) CREATE OR REPLACE FUNCTION article_display(anyelement, anyelement) RETURNS SETOF samle AS $$ DECLARE articleRow sample%ROWTYPE; BEGIN FOR articleRow IN SELECT comments FROM theirry.articles ORDER BY article_id DESC LIMIT $1 OFFSET $2 LOOP RETURN NEXT articleRow; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; Thanks, J ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [ADMIN] plpgsql question
On Tue, Aug 30, 2005 at 12:15:54PM -0400, Postgres Admin wrote: > > Can I do something like this: It's good that you gave an example, but it would also be good to give a summary of what you're trying to do and what trouble you're having so people don't have to guess. > CREATE TABLE sample (id SERIAL, node INTEGER, parent INTEGER); > INSERT INTO sample(node,parent) VALUES(1,0); > INSERT INTO sample(node,parent) VALUES(2,0); > INSERT INTO sample(node,parent) VALUES(3,1); > INSERT INTO sample(node,parent) VALUES(4,3) > > CREATE OR REPLACE FUNCTION article_display(anyelement, anyelement) > RETURNS SETOF samle AS $$ I assume you want to return "SETOF sample", not "samle". When posting code please post the actual code you're running so typos don't distract from the real problem (unless a typo *is* part of the problem). > DECLARE > articleRow sample%ROWTYPE; > BEGIN > FOR articleRow IN SELECT comments > FROM theirry.articles You're selecting a column of one table (theirry.articles) into a row type variable of another table (sample). If the value of comments can't be converted to an integer (the type of sample's first column) then you'll get a syntax error. And what about the other columns of sample? What are you really trying to do here? > ORDER BY article_id > DESC LIMIT $1 > OFFSET $2 LOOP If you're using the function's arguments like this, why did you declare them as anyelement instead of integer? > RETURN NEXT articleRow; > END LOOP; > RETURN; > END; > $$ LANGUAGE plpgsql; It's not clear what you're trying to do nor what problems you're having. Is this example real or a contrived facsimile of what you're really trying to do? Could you provide some more information? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [ADMIN] plpgsql question
I have data in one table called articles and I would like to make a function in which takes certain data from it and display the results. Example: CREATE TABLE articles ( article_id serial, title varchar(200), posted timestamp, article_subject varchar(200), article_body text, allow_comments boolean, comments smallint ); I understand one way to display a results I would like is creating a TYPE with the columns needed. CREATE TYPE articles_output AS ( article_id int title varchar(200), article_body text, comments smallint ); Now I would like the function to display data using the LIMIT and OFFSET option ex: SELECT title, article_body, comments FROM articles ORDER BY article_id DESC *LIMIT 4 OFFSET 0*; this is function I created: CREATE OR REPLACE FUNCTION article_display(integer, integer) RETURNS SETOF article_output AS $$ DECLARE articleRow article_output%ROWTYPE; sampleRow RECORD; BEGIN FOR sampleRow IN SELECT title, article_body, comments FROM articles ORDER BY article_id DESC LIMIT $1 OFFSET $2 LOOP RETURN NEXT sampleRow; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; this is the error -> ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "article_sample" line 10 at return next Can I do this or are there better options? Thanks for the help, J ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] booleans and nulls
On Sat, 2005-08-20 at 21:25, Matt L. wrote: > Out of curiousity, > > 1. Does a boolean column occupy 1byte of disk whether > or not the value is null or not? No. Nulls are stored, one bit per, to a byte at a time. I.e. if you have 8 null fields, they are stored in the same byte. > 2. Is matching on IS NULL or = 0 more efficient? Generally, =0 is EASIER to implement. This is because IS NULL is not directly indexable. At least it wasn't in the past. 8.0 or 8.1 may have made it so. The reason for this is that indexing requires a list of operators, and IS NULL isn't really an operator, but syntax, so it doesn't map to an operator like = < > >= etc... However, you can index on partials, so it was possible to make an index that was like this: create index a_dx on a (boofield) where boolfield IS NULL and then IS NULL was indexed. But at the cost of an additional index to maintain. OTOH, if most of the fields are not null, and the occasional null is what you're looking for, then it's a good idea. If you've got 50/50 distribution of nulls and not nulls, indexing on nulls makes no sense, since you'll never actually use the index because it will always be cheaper to pull by seq scan, except in some clustered index situs. > 3. If I ix'd columns w/ null does postgres know > whatevers not indexed is null or would their be no > point? Actually, it indexes the nulls, it just has a hard time using the index due to the above operator mapping issue. To use the index with NULL / NOT NULL requires the above mentioned partial index. > I currently utilize null fields as 'not a value' has > meaning in a program i've been working on as I don't > want to put false in every column when i only need a > couple with a true/false value. That's a good idea, as it saves space as well. That's pretty much what NULL was meant for. > I'm not joining tables on NULLS, just filtering w/ > them. Then look at the index / where IS (NOT) NULL solution, and let us know how that works. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] sqlstate 02000 while declaring cursor/freeing prepared
Just so that we can snip this thread, we've confirmed that free cursor
and free statement do not affect sqlca structure elements sqlcode and
sqlstate.
Michael Fuhr wrote:
On Mon, Aug 29, 2005 at 04:39:36PM -0500, andy rost wrote:
I worked on my problem a little further and have a little more
information to share. The declare statement that fails consistently
follows a select statement that returns zero rows (and sqlcode 100 and
sqlstate '02000'). If I ommit the select statement from the code or set
sqlcode to 0 before calling the declare statement, the declare statement
works fine.
It appears as though the declare statement is not updating the sqlca
structure. Is this by design for the ecpg options that I'm using? Did I
pick up bad habits while using Informix?
Apparently since PostgreSQL doesn't actually have an OPEN statement,
the ECPG code generator doesn't issue the DECLARE until the code
OPENs the cursor. Observe:
% cat foo.pgc
int
main(void)
{
EXEC SQL CONNECT TO DEFAULT;
printf("before DECLARE\n");
EXEC SQL DECLARE curs CURSOR FOR SELECT 1;
printf("after DECLARE, before OPEN\n");
EXEC SQL OPEN curs;
printf("after OPEN\n");
EXEC SQL CLOSE curs;
EXEC SQL DISCONNECT;
return 0;
}
% ecpg foo.pgc
% cat foo.c
...
printf("before DECLARE\n");
/* declare curs cursor for select 1 */
#line 7 "foo.pgc"
printf("after DECLARE, before OPEN\n");
{ ECPGdo(__LINE__, 0, 1, NULL, "declare curs cursor for select 1 ",
ECPGt_EOIT, ECPGt_EORT);}
#line 9 "foo.pgc"
printf("after OPEN\n");
...
Notice that "after DECLARE" actually comes *before* the DECLARE
statement is issued, so references to sqlcode and sqlstate would
see values from a previous command. I don't know if DECLARE is
supposed to affect error codes or not; I'd have to consult the
standard.
--
Andrew Rost
National Operational Hydrologic Remote Sensing Center (NOHRSC)
National Weather Service, NOAA
1735 Lake Dr. West, Chanhassen, MN 55317-8582
Voice: (952)361-6610 x 234
Fax: (952)361-6634
[EMAIL PROTECTED]
http://www.nohrsc.nws.gov
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] [ADMIN] plpgsql question
On Tue, Aug 30, 2005 at 01:59:04PM -0400, Postgres Admin wrote: > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "article_sample" line 10 at return next You don't show what you did to get this error, but I'd guess you called the function like this: SELECT article_display(10, 0); -- wrong You should have called it like this: SELECT * FROM article_display(10, 0); The function as posted has a few other problems, like returning the wrong type (you return a RECORD variable instead of an article_output variable) and not selecting enough columns for the declared return type. The return type also doesn't match the type shown in the CREATE TYPE statement, and the CREATE TYPE statement fails with a syntax error due to a missing comma. As I mentioned previously, please post the *exact* code you're running so typographic errors in the message don't distract from the real problem. Those typos prevent people from loading the code into their own database so they can test it unless they correct the mistakes, and then they can't be sure they're running the same thing you are. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
