Re: [SQL] SQL problem with aggregate functions.
> > I've got a table in which there is a field that can have one amongst 3 > possible values : D, R, X. Is it possible to get in one query the count of > this different values.Please, note that I don't want to have a querry like > this : > "select count (*) from tab group by f1;", cause i want to get all the possible > count values in one row (these data are already grouped on another field). > To give a more accurate example, here is what I want to retrieve : > > Field group | count of D | count of R | count of X. > > Any clues ? > -- What about something like SELECT SUM(f1_d) AS count_d, SUM(f1_r) AS count_r, SUM(f1_x) AS count_x FROM ( SELECT CASE WHEN f1 = 'D' THEN 1 ELSE 0 END AS f1_d, CASE WHEN f1 = 'R' THEN 1 ELSE 0 END AS f1_r, CASE WHEN f1 = 'X' THEN 1 ELSE 0 END AS f1_x FROM tab ) AS foo ; Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] XML to Postgres conversion
Hello. I am trying to figure out how to import xml documents into a postgres database. I am running PostgreSql 7.1.3 on Red Hat 7.2 at the moment. I have several dynamic xml documents that I want imported into the database on a regular basis. From my research so far, I know that there is middleware available to perform this, but am having some difficulty in finding the actual applications. I am trying to stay with open source applications, if possible. Can anyone give me any suggestions or resources to pull from? Thanks, N. Hill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] list of tables ?
can anyone point me in the right direction ? i need to list all the tables in a database. i've looked at pgadmin_tables which is empty and pga_schema whihc contains a sinlge row i don't want to parse ... is there an easier way t get a list of tables ? i'm on 7.2 ta, Steve Brett ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] list of tables ?
On Thu, 11 Jul 2002, Steve Brett wrote: > can anyone point me in the right direction ? > > i need to list all the tables in a database. > > i've looked at pgadmin_tables which is empty and pga_schema whihc contains a > sinlge row i don't want to parse ... > > is there an easier way t get a list of tables ? > > i'm on 7.2 select * from pg_tables; > > ta, > > Steve Brett > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] list of tables ?
On Thursday 11 July 2002 16:00, Steve Brett wrote: > can anyone point me in the right direction ? > > i need to list all the tables in a database. > > i've looked at pgadmin_tables which is empty and pga_schema whihc contains > a sinlge row i don't want to parse ... > > is there an easier way t get a list of tables ? Start psql with the -E option and issue \dt This gives you the statement(s) used internally by psql. Ian Barwick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] list of tables ? -update to question ...
sorry ... i didn't make myself clear ... i have of course come across \dt before ... what i meant was via sql as in 'select tablelist from ' Steve > -Original Message- > From: Stephane Schildknecht [mailto:[EMAIL PROTECTED]] > Sent: 11 July 2002 15:06 > To: Steve Brett > Subject: Re: [SQL] list of tables ? > > > Le jeu 11/07/2002 à 16:00, Steve Brett a écrit : > > can anyone point me in the right direction ? > > > > i need to list all the tables in a database. > > > > i've looked at pgadmin_tables which is empty and pga_schema > whihc contains a > > sinlge row i don't want to parse ... > > > > is there an easier way t get a list of tables ? > > > > i'm on 7.2 > > > > ta, > > > > Steve Brett > \d > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] list of tables ?
thanks. Steve > -Original Message- > From: Achilleus Mantzios [mailto:[EMAIL PROTECTED]] > Sent: 11 July 2002 15:10 > To: Steve Brett > Cc: Pgsql-Sql (E-mail) > Subject: Re: [SQL] list of tables ? > > > On Thu, 11 Jul 2002, Steve Brett wrote: > > > can anyone point me in the right direction ? > > > > i need to list all the tables in a database. > > > > i've looked at pgadmin_tables which is empty and pga_schema > whihc contains a > > sinlge row i don't want to parse ... > > > > is there an easier way t get a list of tables ? > > > > i'm on 7.2 > > select * from pg_tables; > > > > > ta, > > > > Steve Brett > > > > > > > > ---(end of > broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > -- > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > tel:+30-10-8981112 > fax:+30-10-8981877 > email: [EMAIL PROTECTED] > [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] XML to Postgres conversion
Look at contrib/xml at http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/xml/. I never used this, but it might be useful. George Essig > Hello. > > I am trying to figure out how to import xml documents into a postgres > database. I am running PostgreSql 7.1.3 on Red Hat 7.2 at the moment. > I have several dynamic xml documents that I want imported into the > database on a regular basis. From my research so far, I know that > there is middleware available to perform this, but am having some > difficulty in finding the actual applications. I am trying to stay > with open source applications, if possible. Can anyone give me any > suggestions or resources to pull from? > > Thanks, > > N. Hill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] how to inherits the references...
hi all, I've created some table with the inharitance, something like: CREATE TABLE sigles( sigle varchar(255) PRIMARY KEY ); CREATE TABLE cars( UNIQUE (sigle) )INHERITS (sigles); CREATE TABLE used_cars( old_owner text, kmtext, model text, year text, PRIMARY KEY (sigle) )INHERITS (cars); CREATE TABLE new_cars( model text, type text, some_other text, PRIMARY KEY (sigle) )INHERITS (cars); then I have a table of owner (with some fields) that does not inherits nothing. and then I have a table for the relation between used_cars and old_owner: CREATE TABLE cars_owner_relations( cartext REFERENCES used_cars (sigle), owner text REFERENCES old_owner (id) ); now, when I insert used_cars it also create a sigle inside the "sigles" table, and this is OK, but when I insert a record inside the cars_owner_relations it says: ERROR: referential integrity violation - key referenced from cars_owner_relations not found in sigles as if the sigles where not in the "sigles" table, but it's there! it's probably because of the way in which psql threats the inheritance. my question is (finally): is there some workaround for this?? or: am I making some mistakes?? ok, thanks and sorry for my english... danilo __ Scarica il nuovo Yahoo! Messenger: con webcam, nuove faccine e tante altre novità. http://it.yahoo.com/mail_it/foot/?http://it.messenger.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] XML to Postgres conversion
Hi, Hey wow the contrib/xml looks most impresive. I'm so stoopid I've being looking around for xml solutions with even knowing about the contrib/xml in my beloved postgreSQL source. Thanks for the tip. Cheers Rudi. > Look at contrib/xml at > http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/xml/. I never > used this, but it might be useful. > > George Essig > > > Hello. > > > > I am trying to figure out how to import xml documents into a postgres > > database. I am running PostgreSql 7.1.3 on Red Hat 7.2 at the moment. > > I have several dynamic xml documents that I want imported into the > > database on a regular basis. From my research so far, I know that > > there is middleware available to perform this, but am having some > > difficulty in finding the actual applications. I am trying to stay > > with open source applications, if possible. Can anyone give me any > > suggestions or resources to pull from? > > > > Thanks, > > > > N. Hill > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pg_restore cannot restore index
On this point, I'd like to ask:
1. where I can download this new version?
2. does
pg_restore --index=aa --dbname=test /bjm/x
works also???
Because
pg_restore --table=mytable --dbname=mydb mydumpfile
doesn't work!
I got same error msg.
Jie Liang
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 10, 2002 7:06 PM
To: Jie Liang
Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: pg_restore cannot restore index
Jie Liang wrote:
> Thanks!
> But I did not make long form works also, is it:
> pg_restore --index="\"indexname\"" --dbname=mydb mydumpfile
> ???
> msg:
> pg_restore:[archiver] could open input file: No such file or directory
Strange. I found a few more problems with the getopt values in
pg_restore.c not matching the 'case' statement or the documentation.
I got it working here with my patched version using:
pg_restore -I aa -d test /bjm/x
It can't find the file? I didn't fix anything in that area. I am
confused how that could be messed up. I don't see any other meaningful
changes to pg_restore except the quote fixes I did. Are you sure the
file is correct?
Thanks for pointing these things out. It is a big help. Now I am
starting to wonder what else is wrong in the code. :-)
>
>
> could you give out a example of long form
>
>
> Thanks again.
>
>
> Jie Liang
>
> -Original Message-
> From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 09, 2002 7:59 PM
> To: Jie Liang
> Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
> Subject: Re: pg_restore cannot restore index
>
>
>
> Yep, documentation is wrong. Documentation patch attached and applied.
> Also, in 7.3 you will not need the weird quoting for objects.
>
>
---
>
> Jie Liang wrote:
> > Another possible bug:
> > pg_restore -i "\"indexname\"" -d mydb mydumpfile
> > msg:
> > pg_restore: connecting to database for restore
> > pg_restore: creating FUNCTION "plpgsql_call_handler" ()
> > pg_restore: [archiver (db)] could not execute query: ERROR: function
> > plpgsql_call_handler already exists with same argument types
> > pg_restore: *** aborted because of error
> >
> > I read the pg_restore.c source code, I found:
> > #ifdef HAVE_GETOPT_LONG
> > struct option cmdopts[] = {
> > {"clean", 0, NULL, 'c'},
> > {"create", 0, NULL, 'C'},
> > {"data-only", 0, NULL, 'a'},
> > {"dbname", 1, NULL, 'd'},
> > {"file", 1, NULL, 'f'},
> > {"format", 1, NULL, 'F'},
> > {"function", 1, NULL, 'P'},
> > {"host", 1, NULL, 'h'},
> > {"ignore-version", 0, NULL, 'i'},
> > {"index", 1, NULL, 'I'},
> > So, -i may be mapped wrong, however, -I is illegal option.
> >
> > Thanks!
> >
> >
> > Jie Liang
> >
> >
> >
> > -Original Message-
> > From: Jie Liang [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, July 03, 2002 12:03 PM
> > To: 'Jan Wieck'; Jie Liang
> > Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
> > Subject: Re: [SQL] pg_restore cannot restore function
> >
> >
> >
> > OK, we figured it out.
> > The problem is the documentation confused me!!!
> > In man page of pg_restore:
> > -P function-name
> > --function=function name
> > Specify a procedure or function to be restored.
> >
> > User will assume that syntax of restoring a function is same as
> > restoring a table, but it's not true, it's slightly different.
> > To restore a table:
> > pg_restore -Rxt mytable -d mydb2 dbf
> > works, but to restore a function:
> > pg_restore -P myfunction -d mydb2 dbf
> > won't work, and you need to use:
> > pg_restore -P "\"myfunction\" (args and type)" -d mydb2 dbf
> > to make it work!
> >
> >
> > I believe that the man page of pg_restore should be improved.
> >
> >
> > Thanks.
> >
> >
> >
> > Jie Liang
> >
> >
> >
> > -Original Message-
> > From: Jan Wieck [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, July 01, 2002 11:14 AM
> > To: Jie Liang
> > Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
> > Subject: Re: [SQL] pg_restore cannot restore function
> >
> >
> > Jie Liang wrote:
> > >
> > > Oops,my OS is FreeBSD4.3 PostgreSQL7.2
> >
> > I cannot see such an error message in the pg_restore sources at all. Are
> > you sure to use the right versions together?
> >
> >
> > Jan
> >
> > >
> > > Thanks
> > >
> > > Jie Liang
> > >
> > > -Original Message-
> > > From: Jie Liang
> > > Sent: Friday, June 28, 2002 1:46 PM
> > > To: 'Jan Wieck'
> > > Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]';
'[EMAIL PROTECTED]'
> > > Subject: RE: [SQL] pg_restore cannot restore function
> > >
> > > No any error msg in the logfile, I didn't see any create function
> > statement
> > > in my logfile which I enabled the query log.
> > > This function
Re: [SQL] how to inherits the references...
On Thu, 11 Jul 2002, [iso-8859-1] frederik nietzsche wrote: > as if the sigles where not in the "sigles" table, but > it's there! > it's probably because of the way in which psql threats > the inheritance. > my question is (finally): is there some workaround for > this?? or: am I making some mistakes?? Foreign keys don't inherit to children table on either the fk or pk side. Note also that the primary keys in the above will not guarantee that sigle is unique across the whole set, only across each table individually. Pretty much the only workaround I know of is to make a table with the key columns and have each of the tables in the inheritance tree have its key columns reference that and anything that wants to reference the inheritance tree references the other table instead. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [HACKERS] please help on query
[moving to pgsql-sql]
On Thu, 11 Jul 2002 17:22:14 +0200, "Luis Alberto Amigo Navarro"
<[EMAIL PROTECTED]> wrote:
>I can't improve performance on this query:
>
>SELECT
> supplier.name,
> supplier.address
>FROM
> supplier,
> nation
>WHERE
> supplier.suppkey IN(
> SELECT
> partsupp.suppkey
> FROM
> partsupp
> WHERE
> partsupp.partkey IN(
>SELECT
> part.partkey
>FROM
> part
>WHERE
> part.name like 'forest%'
> )
> AND partsupp.availqty>(
>SELECT
> 0.5*(sum(lineitem.quantity)::FLOAT)
>FROM
> lineitem
>WHERE
> lineitem.partkey=partsupp.partkey
> AND lineitem.suppkey=partsupp.partkey
^^^
suppkey ???
> AND lineitem.shipdate>=('1994-01-01')::DATE
> AND lineitem.shipdate<(('1994-01-01')::DATE+('1 year')::INTERVAL)::DATE
> )
> )
> AND supplier.nationkey=nation.nationkey
> AND nation.name='CANADA'
>ORDER BY
> supplier.name;
Luis,
rules of thumb: "Avoid subselects; use joins!" and "If you have to use
subselects, avoid IN, use EXISTS!"
Let's try. If partkey is unique in part, then
| FROM partsupp
| WHERE partsupp.partkey IN (SELECT part.partkey
can be replaced by
FROM partsupp ps, part p
WHERE ps.partkey = p.partkey
or
partsupp ps INNER JOIN part p
ON (ps.partkey = p.partkey AND p.name LIKE '...')
When we ignore "part" for now, your subselect boils down to
| SELECT partsupp.suppkey
| FROM partsupp
| WHERE partsupp.availqty > (
|SELECT 0.5*(sum(lineitem.quantity)::FLOAT)
|FROM lineitem
|WHERE lineitem.partkey=partsupp.partkey
| AND lineitem.suppkey=partsupp.suppkey
| AND lineitem.shipdate BETWEEN ... AND ...
| )
which can be rewritten to (untested)
SELECT ps.suppkey
FROM partsupp ps, lineitem li
WHERE li.partkey=ps.partkey
AND li.suppkey=ps.suppkey
AND lineitem.shipdate BETWEEN ... AND ...
GROUP BY ps.partkey, ps.suppkey
HAVING min(ps.availqty) > 0.5*(sum(lineitem.quantity)::FLOAT)
^^^
As all ps.availqty are equal in one group, you can as well
use max() or avg().
Now we have left only one IN:
| WHERE supplier.suppkey IN (
| SELECT partsupp.suppkey FROM partsupp WHERE )
Being to lazy to find out, if this can be rewritten to a join, let`s
apply rule 2 here:
WHERE EXISTS (
SELECT ... FROM partsupp ps
WHERE supplier.suppkey = ps.suppkey
AND )
HTH, but use with a grain of salt ...
>Sort (cost=2777810917708.17..2777810917708.17 rows=200 width=81)
BTW, how many years are these? :-)
Servus
Manfred
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] list of tables ? -update to question ...
Steve Brett wrote: > > sorry ... i didn't make myself clear ... > > i have of course come across \dt before ... > > what i meant was via sql as in 'select tablelist from perhaps ?>' What about: SELECT * FROM pg_tables; Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [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] [HACKERS] please help on query
On Thu, 11 Jul 2002 19:40:46 +0200, "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> wrote: >I've tried [reformatted to fit on one page] | SELECT supplier.name, supplier.address | FROM supplier, nation, lineitem You already found out that you do not need lineitem here. | WHERE EXISTS( | SELECT partsupp.suppkey | FROM partsupp,lineitem | WHERE |lineitem.partkey=partsupp.partkey |AND lineitem.suppkey=partsupp.partkey I still don't believe this suppkey=partkey |AND lineitem.shipdate [...] |AND EXISTS( SELECT part.partkey |FROM part WHERE part.name like 'forest%') This subselect gives either true or false, but in any case always the same result. You might want to add a condition AND part.partkey=partsupp.partkey Are you sure partkey is not unique? If it is unique you can replace this subselect by a join. | GROUP BY partsupp.partkey,partsupp.suppkey | HAVING min(availqty)>(0.5*(sum(lineitem.quantity)::FLOAT)) | ) | AND supplier.nationkey=nation.nationkey | AND nation.name='CANADA' | ORDER BY supplier.name; >as you said and something is wrong >Sort (cost=1141741215.35..1141741215.35 rows=240049 width=81) The cost is now only 1141741215.35 compared to 2777810917708.17 before; this is an improvement factor of more than 2000. So what's your problem? ;-) Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pg_restore cannot restore index
What parameter I should change in order to make postmaster taking CPU as much as possible? Maybe I should ask: how can I make big tables equijoin faster? I have a serveral tables that contain more 2.5 million records, I need to equijoin those tables often. Thanks! Jie Liang ---(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] SQL problem with aggregate functions.
I would suggest something like
select sum(case when f1 = 'D' then 1 else 0 end) as D_COUNT,
sum(case when f1 = 'R' then 1 else 0 end) as R_COUNT,
sum(case when f1 = 'X' then 1 else 0 end) as X_COUNT
from tab
where f1 in ('D','R','X')
Not sure what the "field group" represents.
HTH,
Loyd
On Thu, 11 Jul 2002 10:37:40 METDST, Christoph Haller <[EMAIL PROTECTED]> wrote:
>>
>> I've got a table in which there is a field that can have one amongst 3
>> possible values : D, R, X. Is it possible to get in one query the count of
>> this different values.Please, note that I don't want to have a querry like
>> this :
>> "select count (*) from tab group by f1;", cause i want to get all the possible
>> count values in one row (these data are already grouped on another field).
>> To give a more accurate example, here is what I want to retrieve :
>>
>> Field group | count of D | count of R | count of X.
>>
>> Any clues ?
>> --
>What about something like
>
>
>SELECT SUM(f1_d) AS count_d,
> SUM(f1_r) AS count_r,
> SUM(f1_x) AS count_x
>FROM (
> SELECT CASE WHEN f1 = 'D' THEN 1 ELSE 0 END AS f1_d,
>CASE WHEN f1 = 'R' THEN 1 ELSE 0 END AS f1_r,
>CASE WHEN f1 = 'X' THEN 1 ELSE 0 END AS f1_x
> FROM tab ) AS foo ;
>
>Regards, Christoph
>
>---(end of broadcast)---
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
--
"Why, you can even hear yourself think." --Hobbes
"This is making me nervous. Let's go in." --Calvin
[EMAIL PROTECTED] ICQ#504581 http://www.blackrobes.net/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Please, HELP! Why is the query plan so wrong???
Hi, everybody! Here is the problem: test=# create table fb (a int, b int, c datetime); CREATE test=# create table fbr (a int, c datetime, d int); CREATE test=# create unique index fb_idx on fb(b); CREATE test=# create index fbr_idx on fbr(a,c) where d is null; CREATE test=# set enable_seqscan=off; SET VARIABLE rapidb=# explain select * from fb, fbr where fb.b=0 and fb.a=fbr.a and fb.c=fbr.c and fbr.d is null; NOTICE: QUERY PLAN: Hash Join (cost=10005.82..11015.87 rows=1 width=32) -> Seq Scan on fbr (cost=1.00..11010.00 rows=5 width=16) -> Hash (cost=5.81..5.81 rows=1 width=16) -> Index Scan using fb_idx on fb (cost=0.00..5.81 rows=1 width=16) Could someone PLEASE explain to me, why doesn't it want to use the index on fbr? If I get rid of the join, then it works: test=# explain select * from fbr where a=1 and c=now() and d is null; NOTICE: QUERY PLAN: Index Scan using fbr_idx on fbr (cost=0.00..5.82 rows=1 width=16) What's the catch??? Any help would be greatly appreciated! Thanks! Dima ---(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] PostgreSQL - contrib/xml
Hello :-) I'm not sure how to compile in the contrib/xml into Posgtresql. I do compile Postgres on my Debian box but I'm not sure about the flags etc to also compile the contrib stuff. I know the standard compile commands / options so in order to get the xml compiled can you tell me what to add. I'm using: ./configure --with-perl --with-openssl make make install Thanks kindly Regards Rudi. ---(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] Please, HELP! Why is the query plan so wrong???
I believe that SQL will use the index of join 'key' when you join the tables if have any, in your query the (a,c) is the join key but d is not. Jie Liang -Original Message- From: Dmitry Tkach [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 11, 2002 3:51 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [SQL] Please, HELP! Why is the query plan so wrong??? Hi, everybody! Here is the problem: test=# create table fb (a int, b int, c datetime); CREATE test=# create table fbr (a int, c datetime, d int); CREATE test=# create unique index fb_idx on fb(b); CREATE test=# create index fbr_idx on fbr(a,c) where d is null; CREATE test=# set enable_seqscan=off; SET VARIABLE rapidb=# explain select * from fb, fbr where fb.b=0 and fb.a=fbr.a and fb.c=fbr.c and fbr.d is null; NOTICE: QUERY PLAN: Hash Join (cost=10005.82..11015.87 rows=1 width=32) -> Seq Scan on fbr (cost=1.00..11010.00 rows=5 width=16) -> Hash (cost=5.81..5.81 rows=1 width=16) -> Index Scan using fb_idx on fb (cost=0.00..5.81 rows=1 width=16) Could someone PLEASE explain to me, why doesn't it want to use the index on fbr? If I get rid of the join, then it works: test=# explain select * from fbr where a=1 and c=now() and d is null; NOTICE: QUERY PLAN: Index Scan using fbr_idx on fbr (cost=0.00..5.82 rows=1 width=16) What's the catch??? Any help would be greatly appreciated! Thanks! Dima ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PostgreSQL - contrib/xml
The usual way to install a contrib is to run configure with whatever args you want in the root postgres source dir. Then: cd contrib/xml gmake all gmake install Sort of thing. Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Rudi Starcevic > Sent: Friday, 12 July 2002 7:27 AM > To: [EMAIL PROTECTED] > Subject: [SQL] PostgreSQL - contrib/xml > > > Hello :-) > > I'm not sure how to compile in the contrib/xml into Posgtresql. > I do compile Postgres on my Debian box but I'm not sure about the flags > etc to > also compile the contrib stuff. > > I know the standard compile commands / options so in order to get the xml > compiled can you tell me what to add. > I'm using: > > ./configure --with-perl --with-openssl > make > make install > > Thanks kindly > Regards > Rudi. > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Query kill
Hello, If I write a query that is inefficient or in an eternal loop how do I stop it without restarting the postmaster ? I can see many postmaster processed appearing in the output of the 'ps' command. Do I need to stop/kill them all or can I stop just the query I want ? Thanks heaps Rudi. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query kill
Rudi Starcevic wrote: > Hello, > > If I write a query that is inefficient or in an eternal loop how > do I stop it without restarting the postmaster ? > > I can see many postmaster processed appearing in the output of the 'ps' > command. > Do I need to stop/kill them all or can I stop just the query I want ? Just send a SIGINT to the process. That simulates a ^C, which works too from the client like psql. -- 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 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
