Re: [SQL] Question about indexing!

[Jeong Jaeick, 정재익]

pgsql_bbs table has about 15,000 rows!
And almost of them are satify (topic='qna' and deleted<2) condition.

This explain result have a large cost.
I want to low this query cost.

Thanks for your concern :-)

>> select * from pgsql_bbs where topic = 'qna' and deleted < 2
>>order by gid desc, pos asc limit 20, 0;
>>
>> But this query is not using index!
>>
>> The next is result of explain of this query:
>>
>> dsn=# explain select * from pgsql_bbs where topic = 'qna' and deleted < 2
>> dsn-#order by gid desc, pos asc limit 20, 0;
>> NOTICE:  QUERY PLAN:
>>
>> Limit  (cost=15.71..15.71 rows=11 width=245)
>>   ->  Sort  (cost=15.71..15.71 rows=11 width=245)
>> ->  Index Scan using pgsql_topic_deleted on pgsql_bbs  (cost=0.00..15.51 
>rows=11 widt
>h=245)
>
>It appears to be using the topic_deleted index according to this
>explain output.
>How many rows actually have topic='qna' and deleted<2?
-- 

mailto:[EMAIL PROTECTED]
http://database.sarang.net
Dept of Neurosurgery, Dong-eui Medical Center


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] 7.0.3 and 7.1.3 different results?

[Josh Berkus]

Stephan,

> Actually it seems to me that one NULL row is correct...
> 
> 7.9 
>  1) Case:
>   a) If T is not a grouped table, then

You are correct according to the SQL spec.  

However, depending on what interface I use for the database, I can get
an empty recordset rather than a single NULL row.  This has more to do
with the interface translation (such as ODBC) than it does with what
PostgreSQL is returning.

Partly I think this is DB vendors and interpreters getting confused over
a consistency issue between SELECT SUM() and SELECT SUM() ... GROUP BY.  

SELECT sum(totalamount)
FROM invoices
WHERE invoice_no > invoice_no;

---
NULL

SELECT invoice_no, sum(totalamount)
FROM invoices
WHERE invoice_no > invoice_no
GROUP BY invoice_no;

invoice_no|sum
--
(empty recordset)

Obviously not particularly troublesome behavior, as these results seem
to be more or less consistent across most vendor implementations.  Plus
in many languages the tests for NULL and empty recordset overlap or are
easily combined.  And this is pretty clearly defined in SQL 92, as you
point out.

-Josh

P.S. Can you answer my question about indexing, please please?  

__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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] to_date/to timestamp going to BC

[jason . servetar]

Can someone tell me if this is a bug with the date functions or am I using
them incorrectly?
If anyone has a workaround for this I could use some help this data
conversion.

dev=> create table test_date (dt varchar(100));
CREATE

dev=> insert into test_date values ('March 11, 1997');
INSERT 706020 1

dev=> select dt, to_date(dt, 'Month dd, '), to_timestamp(dt, 'Month dd,
') from test_date;
   dt   |to_date| to_timestamp  
+---+---
 March 11, 1997 | 0001-03-19 BC | 0001-03-19 BC

drw_dev=> select version();
version 

 PostgreSQL 7.1 on sparc-sun-solaris2.6, compiled by GCC 2.95.2
(1 row)




---(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]maximum parameters limit to function & manipulating array in plpgsql

[[EMAIL PROTECTED]]

Hi,
 
I have 2 major problems bothering me and probably u 
are the best person to help me out :-
 
1) 
Well I read one of ur solutions to sending more 
tham 16 parameters to a function in plpgsql. I have changed the value of 
FUNC_MAX_ARGS (a/k/a INDEX_MAX_KEYS) in 
\usr\local\plpgsql\include\config.h after 
stoping the postmaster service.
 On restarting the service after having 
changed config.h I am atill experiencing the same problem. What should I do 
now?
 
2) 
Secondly I am trying to use arrays as below 
:
 
create function ftest(integer[]) returns integer[] 
as'declaren 
$1%type;begin.
    ...
    n:={1,12,11};    
return n;end;'language 'plpgsql';
 
If I declare n as integer[3] it gives a compilation 
error. So I tried it as above. Can I do any array manipulations at all in 
plpgsql like assigning,copying etc values in to other arrays in plpgsql. If so 
how ??
 
Do help me out since I have got stuck 
with both these problems.
 
Regards,
Advid 
Newgen Software Tech

[SQL]

[postgresql]

Please can someone help

 I tried to subccribe to pgsl-admin but I have been unable. 

I just upgraded to Mac osx 10.1. When I try to configure --with-tcl I get an error 
that 'wish' can not be found. Where do I get this? (this is not so important but I 
would like to use pgaccess)

2.  below is the error I am getting from 'make' (included the output from make -v)
any ideas (I know that I can go back to osx version 10.0.4 but 10.1 is much faster 
writing to the  screen.  

I apologize for posting this here. I just can not seem to get the confirmation to the 
other groups.

Ted



cc -traditional-cpp -g -O2 -Wall -Wmissing-prototypes 
-Wmissing-declarations -bundle -undefined suppress -bundle 
-undefined suppress fe-auth.o fe-connect.o fe-exec.o fe-misc.o 
fe-print.o fe-lobj.o pqexpbuffer.o dllist.o pqsignal.o   -o libpq.so.2.1
/usr/bin/ld: -undefined error must be used when 
-twolevel_namespace is in effect
make[3]: *** [libpq.so.2.1] Error 1
make[2]: *** [all] Error 2
make[1]: *** [all] Error 2
make: *** [all] Error 2
[localhost:/Users/postgres/postgresql-7.1.3] root# make -v
GNU Make version 3.79, by Richard Stallman and Roland McGrath.
Built for powerpc-apple-darwin1.4
Copyright (C) 1988, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99
Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS 
FOR A
PARTICULAR PURPOSE.

Report bugs to <[EMAIL PROTECTED]>.



---(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] ORDER BY case insensitive?

[Jeff Boes]

In article , "Bob
Swerdlow" <[EMAIL PROTECTED]> wrote:

> How do I get the rows sorted in a case insensitive way?

>  SELECT * FROM MyTable ORDER BY Name;

Try 

SELECT * FROM MyTable ORDER BY upper(Name);

(or 'lower(Name)').

-- 
Jeff Boes vox 616.226.9550
Database Engineer fax 616.349.9076
Nexcerpt, Inc.  [EMAIL PROTECTED]

---(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] 7.0.3 and 7.1.3 different results?

[Josh Berkus]

Joel,

> A query such as "select sum(pages) from job_documents where
> delivery_type='print'" returned 0 in version 7.0.3 if there were no
> rows
> matching the query. In 7.1.3 the result is NULL if no rows match the
> query. Why the change? Which result is "correct" according to the
> SQL
> standard?

While I wasn't aware of the change between versions, returning no rows
is correct for any aggregate except COUNT, which returns 0. Although,
now that you mention it, I'm not quite sure why that's the rule. I
mean, shouldn't COUNT return no rows, too?

Goes to show you that the SQL standard isn't even the model of perfect
consistency 

BTW, returning no rows is somewhat different than returning NULL. WHat
you should be seeing is:

SELECT sum(id) FROM tableA WHERE field2 = 'not found';

sum
--

rather than

sum
--
NULL


-Josh













---(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] Question about indexing!

[Stephan Szabo]

On Wed, 3 Oct 2001, [euc-kr] Jeong Jaeick, Á¤ÀçÀÍ wrote:

> pgsql_bbs table has about 15,000 rows!
> And almost of them are satify (topic='qna' and deleted<2) condition.

Ah, so it's getting it wrong.  It *shouldn't* be using that index. :(
[Index scans over most of the table is slower than the sequence
scan.]

Have you run vacuum analyze on this database?  In any case, does
running vacuum analyze change the explain at all (even the estimated
row counts)?

> This explain result have a large cost.
> I want to low this query cost.

BTW, do you mean a large cost in real time?  The explain numbers
don't necessarily reflect an actual time.
Also, you may want to check your sort memory settings to make
sure you're allocating enough (the defaults are generally low).

> >> select * from pgsql_bbs where topic = 'qna' and deleted < 2
> >>order by gid desc, pos asc limit 20, 0;
> >>
> >> But this query is not using index!
> >>
> >> The next is result of explain of this query:
> >>
> >> dsn=# explain select * from pgsql_bbs where topic = 'qna' and deleted < 2
> >> dsn-#order by gid desc, pos asc limit 20, 0;
> >> NOTICE:  QUERY PLAN:
> >>
> >> Limit  (cost=15.71..15.71 rows=11 width=245)
> >>   ->  Sort  (cost=15.71..15.71 rows=11 width=245)
> >> ->  Index Scan using pgsql_topic_deleted on pgsql_bbs  (cost=0.00..15.51 
>rows=11 widt
> >h=245)
> >
> >It appears to be using the topic_deleted index according to this
> >explain output.
> >How many rows actually have topic='qna' and deleted<2?
> -- 
> 
> mailto:[EMAIL PROTECTED]
> http://database.sarang.net
> Dept of Neurosurgery, Dong-eui Medical Center
> 
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] 7.0.3 and 7.1.3 different results?

[Stephan Szabo]

On Wed, 3 Oct 2001, Josh Berkus wrote:

> Joel,
> 
> > A query such as "select sum(pages) from job_documents where
> > delivery_type='print'" returned 0 in version 7.0.3 if there were no
> > rows
> > matching the query. In 7.1.3 the result is NULL if no rows match the
> > query. Why the change? Which result is "correct" according to the
> > SQL
> > standard?
> 
> While I wasn't aware of the change between versions, returning no rows
> is correct for any aggregate except COUNT, which returns 0. Although,
> now that you mention it, I'm not quite sure why that's the rule. I
> mean, shouldn't COUNT return no rows, too?
> 
> Goes to show you that the SQL standard isn't even the model of perfect
> consistency 
> 
> BTW, returning no rows is somewhat different than returning NULL. WHat
> you should be seeing is:

Actually it seems to me that one NULL row is correct...

7.9 
 1) Case:
  a) If T is not a grouped table, then
   Case:
i) If the  contains a  that contains a reference to a column of T or di-
   rectly contains a  that does
   not contain an outer reference, then T is the argument or
   argument source of each such 
   and the result of the  is a table con-
   sisting of 1 row. The i-th value of the row is the value
   specified by the i-th .


---(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] to_date/to timestamp going to BC

[Tom Lane]

[EMAIL PROTECTED] writes:
> Can someone tell me if this is a bug with the date functions or am I using
> them incorrectly?

I get the right thing when I use the right format:

regression=# select dt, to_timestamp(dt, 'FMMonth dd, ') from test_date;
   dt   |  to_timestamp
+
 March 11, 1997 | 1997-03-11 00:00:00-05
(1 row)

However, I'd agree that this shows a lack of robustness in to_timestamp;
it's not objecting to data that doesn't match the format.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] to_date/to timestamp going to BC

[jason . servetar]

Thanks Tom that worked great.
I guess I should have not skipped the
FM prefix section of the date conversion doc.

drw_dev-> to_timestamp(dt, 'FMMonth dd, ') from test_date;
   dt   |  to_date   |  to_timestamp  
++
 March 11, 1997 | 1997-03-11 | 1997-03-11 00:00:00-07

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 03, 2001 3:14 PM
To: Servetar, Jason
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] to_date/to timestamp going to BC 


[EMAIL PROTECTED] writes:
> Can someone tell me if this is a bug with the date functions or am I using
> them incorrectly?

I get the right thing when I use the right format:

regression=# select dt, to_timestamp(dt, 'FMMonth dd, ') from test_date;
   dt   |  to_timestamp
+
 March 11, 1997 | 1997-03-11 00:00:00-05
(1 row)

However, I'd agree that this shows a lack of robustness in to_timestamp;
it's not objecting to data that doesn't match the format.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] SubQuery

[cnliou]

Hi!

I am using MyTable to serve the "next number"
functionality like PGSQL embeded "sequence" offers.
The only difference of the two is MyTable has 2 more
fields - "ID" and "Starting Effective Date".

The UPDATE SQL fails when I am trying to update
record

 ID1 | 2001-08-01| 11

to

 ID1 | 2001-08-01| 12

What mistake have I made?

CN
=
database1=# select * from mytable;
 id  | effectivedate | nextnumber
-+---+
 ID1 | 2001-06-01| 10
 ID1 | 2001-07-01|  3
 ID1 | 2001-08-01| 11
 ID2 | 2001-09-01| 35
(4 rows)

database1=# SELECT EffectiveDate,NextNumber FROM
mytable s1
database1-# WHERE id='ID1' AND EffectiveDate=
database1-# (SELECT MAX(EffectiveDate) FROM mytable
s2
database1(# WHERE s1.id=s2.id AND
s2.EffectiveDate<=CURRENT_DATE);
 effectivedate | nextnumber
---+
 2001-08-01| 11
(1 row)

database1=# UPDATE mytable SET
NextNumber=NextNumber+1
database1-# WHERE id='ID1' AND EffectiveDate=
database1-# (SELECT MAX(s2.EffectiveDate) FROM
mytable s2
database1(# WHERE s2.id=id AND
s2.EffectiveDate<=CURRENT_DATE);
UPDATE 0
database1=# UPDATE mytable SET
NextNumber=NextNumber+1
database1-# WHERE id='ID2' AND EffectiveDate=
database1-# (SELECT MAX(s2.EffectiveDate) FROM
mytable s2
database1(# WHERE s2.id=id AND
s2.EffectiveDate<=CURRENT_DATE);
UPDATE 1
database1=# select * from mytable;
 id  | effectivedate | nextnumber
-+---+
 ID1 | 2001-06-01| 10
 ID1 | 2001-07-01|  3
 ID1 | 2001-08-01| 11
 ID2 | 2001-09-01| 36
(4 rows)


You too can have your own email address from Eurosport.
http://www.eurosport.com






---(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] EXISTS Keyword

[cnliou]

Hello!

I vaguely remember that someone said somewhere that
EXISTS runs faster than IN:

SELECT * FROM table1 where field1 EXISTS (SELECT
field4 FROM table2)

However, all I got from version 7.1.3 is:

ERROR:  parser: parse error at or near "exists"

While below works:

SELECT * FROM table1 where field1 IN (SELECT field4
FROM table2)

Any key points I have missed?

CN


You too can have your own email address from Eurosport.
http://www.eurosport.com






---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] SubQuery

[Stephan Szabo]

On Thu, 4 Oct 2001 [EMAIL PROTECTED] wrote:

> What mistake have I made?
> 
> database1=# UPDATE mytable SET
> NextNumber=NextNumber+1
> database1-# WHERE id='ID1' AND EffectiveDate=
> database1-# (SELECT MAX(s2.EffectiveDate) FROM
> mytable s2
> database1(# WHERE s2.id=id AND
> s2.EffectiveDate<=CURRENT_DATE);
> UPDATE 0

I think you may need to disambiguate the s2.id=id 
in the subquery to s2.id=mytable.id since both 
have ids and it's probably assuming that means
s2.id=s2.id.  
After changing that I get an UPDATE 1.




---(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] EXISTS Keyword

[Bruce Momjian]

> Hello!
> 
> I vaguely remember that someone said somewhere that
> EXISTS runs faster than IN:
> 
> SELECT * FROM table1 where field1 EXISTS (SELECT
> field4 FROM table2)

Move field1 into the subquery and join it to table2.  See the FAQ for an
example.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html