Re: [SQL] [HACKERS] why is postgres estimating so badly?
> > AND part.name LIKE '%green%' > > It's difficult for the planner to produce a decent estimate for the > selectivity of an unanchored LIKE clause, since there are no statistics > it can use for the purpose. We recently changed FIXED_CHAR_SEL in > src/backend/utils/adt/selfuncs.c from 0.04 to 0.20, which would make > this particular case come out better. (I believe the estimate would > work out to about 320, if part is 200K rows; that should be enough to > produce at least some change of plan.) You could try patching your > local installation likewise. Here are the results, worse than before: NOTICE: QUERY PLAN: Sort (cost=25209.88..25209.88 rows=1 width=93) (actual time=1836143.78..1836144.48 rows=175 loops=1) -> Aggregate (cost=25209.85..25209.87 rows=1 width=93) (actual time=1803559.97..1836136.47 rows=175 loops=1) -> Group (cost=25209.85..25209.86 rows=2 width=93) (actual time=1803348.04..1816093.89 rows=325302 loops=1) -> Sort (cost=25209.85..25209.85 rows=2 width=93) (actual time=1803347.97..1804795.41 rows=325302 loops=1) -> Hash Join (cost=25208.43..25209.84 rows=2 width=93) (actual time=1744714.61..1772790.19 rows=325302 loops=1) -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=15) (actual time=13.92..14.84 rows=25 loops=1) -> Hash (cost=25208.42..25208.42 rows=2 width=78) (actual time=1744603.74..1744603.74 rows=0 loops=1) -> Nested Loop (cost=0.00..25208.42 rows=2 width=78) (actual time=139.21..1740110.04 rows=325302 loops=1) -> Nested Loop (cost=0.00..25201.19 rows=2 width=70) (actual time=122.37..1687895.49 rows=325302 loops=1) -> Nested Loop (cost=0.00..25187.93 rows=4 width=62) (actual time=121.75..856097.27 rows=325302 loops=1) -> Nested Loop (cost=0.00..17468.91 rows=1280 width=24) (actual time=78.43..19698.77 rows=43424 loops=1) -> Seq Scan on part (cost=0.00..12399.00 rows=320 width=4) (actual time=29.57..4179.70 rows=10856 loops=1) -> Index Scan using partsupp_pkey on partsupp (cost=0.00..15.79 rows=4 width=20) (actual time=1.17..1.33 rows=4 loops=10856) -> Index Scan using l_partsupp_index on lineitem (cost=0.00..6.02 rows=1 width=38) (actual time=2.83..18.97 rows=7 loops=43424) -> Index Scan using orders_pkey on orders (cost=0.00..3.23 rows=1 width=8) (actual time=2.47..2.50 rows=1 loops=325302) -> Index Scan using supplier_pkey on supplier (cost=0.00..3.01 rows=1 width=8) (actual time=0.08..0.09 rows=1 loops=325302) Total runtime: 1836375.16 msec It looks even worse, another advice?, or maybe a query change. here is the query again: SELECT nation, o_year, CAST((sum(amount))AS NUMERIC(10,2))AS sum_profit FROM( SELECT nation.name AS nation, EXTRACT(year FROM orders.orderdate) AS o_year, lineitem.extendedprice*(1-lineitem.discount)-partsupp.supplycost*lineitem.qu antity AS amount FROM part, supplier, lineitem, partsupp, orders, nation WHERE supplier.suppkey=lineitem.suppkey AND partsupp.suppkey=lineitem.suppkey AND partsupp.partkey=lineitem.partkey AND part.partkey=lineitem.partkey AND orders.orderkey=lineitem.orderkey AND supplier.nationkey=nation.nationkey AND part.name LIKE '%green%' ) AS profit GROUP BY nation, o_year ORDER BY nation, o_year DESC; Thanks and regards ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] how do i import my sql query result to a file
how do i import results of my select query to a file thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how do i import my sql query result to a file
\? will get you a list of the commands in psql. Steve > -Original Message- > From: Joseph Syjuco [mailto:[EMAIL PROTECTED]] > Sent: 18 July 2002 22:47 > To: [EMAIL PROTECTED] > Subject: [SQL] how do i import my sql query result to a file > > > how do i import results of my select query to a file > thanks > > > > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(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] Newbie: Creative use of LIMIT??
On Wednesday 17 Jul 2002 3:20 am, Shmuel A. Kahn wrote: > Assuming I have the following two tables of people and their nicknames, > and that I want to create a list containing UPTO 2 (or any value > greater than 1) nicknames for EACH member of a specific family (Fam > column), how would I do this? > > but am totally clueless on how to impose a limit on the number of rows > to get for each individual family MEMBER. Something along these lines should be what you're after: SELECT * FROM nicknames; id | person_id | nick +---+-- 1 | 1 | Darth 2 | 1 | Lord Vader 3 | 1 | Mr Black-hat 4 | 2 | Luke 5 | 2 | Metal-hand 6 | 2 | Bad-hair boy (6 rows) SELECT * FROM nicknames n1 WHERE n1.id IN ( SELECT n2.id FROM nicknames n2 WHERE n2.person_id=n1.person_id ORDER BY id LIMIT 2 ); id | person_id |nick +---+ 1 | 1 | Darth 2 | 1 | Lord Vader 4 | 2 | Luke 5 | 2 | Metal-hand (4 rows) Note that this is running a separate subquery for each person_id so if you have a large table performance might not be brilliant. The usual advice is to try to rewrite the IN as an EXISTS instead, but I'm not clear on how you'd do that in this case. Actually, looking at it, it might run a separate subquery for each row. Ideally, there'd be some way of having a "PERGROUP LIMIT" imposed, but I'm afraid I don't know of one. - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] about Inheritance...
Hi all, I'm still in trouble with inheritance. Now I've created some triggers and rules for realize something similiar to an inheritance... I MUST do myself all the check for integrity, it seems the only way. But because psql executes a function in a transaction it was not so bad... In practice, if you insert some datas in a child table, YOU must insert it also in the father table, resulting in having two identical data in the father table. if you don't do that, and you insert only in the child table, you can see the data in the father, but if you SELECT from it, seems it's not there... I hope it is intelligible. Any of you have ever dealt with such a thing?? I'd like to hear someother's solutions, if any. I've also a question, Is there any sql statement for identify, knowing it's key value, where is (in which child table) a record in the father table?? I have the key of a record in the father, and I want to know in which child table is the real information ok bye and thaks all... 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] hints or suggestion for optimizer
How can I optimize Postrges SQL when I would like have ordering kolumns without clause ORDER BY - in Oracle you can use hints to suggestion optimizer. for example. SELECT /*+ INDEX_ASC TAB(TAB_PK) +/ * FROM TAB - most efficent SELECT * FROM TAB ORDER BY TAB_PK where TAB_PK - is unique index on KOD column WHERE CLAUSE - without OR ,UNION, accept LIKE, AND etc Does similar mechanizm has Postgres ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to find out if an index is unique?
You could also use describe (in psql environment ) psql mydatabase mydatabase=# \d indexname Index "indexname" Attribute | Type +--- fieldname| datatype unique btree The word "unique" will show up only if the index has the unique qualification, otherwise it will read only "btree". Hope this helps. Ligia "Dirk Lutzebaeck" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > Hello, > > is there a way to ask the system tables if a given index was created > with the unique qualification? I don't want to insert data to try. > > Greetings, > > Dirk > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [HACKERS] why is postgres estimating so badly?
The first thing to point out is that the estimated cost is measured in terms of page reads while the actual time is measured in milliseconds. So even if the cost estimate is accurate it is unlikely that those numbers will be the same. -N -- Nathan C. Burnett Research Assistant, Wisconsin Network Disks Department of Computer Sciences University of Wisconsin - Madison [EMAIL PROTECTED] On Wed, 17 Jul 2002, Luis Alberto Amigo Navarro wrote: > I have a query and estimations and results don´t look similar, here is explain >analyze: > > NOTICE: QUERY PLAN: > > Sort (cost=12443.90..12443.90 rows=1 width=93) (actual time=505331.94..505332.67 >rows=175 loops=1) > -> Aggregate (cost=12443.88..12443.89 rows=1 width=93) (actual >time=472520.29..505326.48 rows=175 loops=1) > -> Group (cost=12443.88..12443.89 rows=1 width=93) (actual >time=472307.31..485173.92 rows=325302 loops=1) > -> Sort (cost=12443.88..12443.88 rows=1 width=93) (actual >time=472307.24..473769.79 rows=325302 loops=1) > -> Nested Loop (cost=12439.25..12443.87 rows=1 width=93) >(actual time=103787.68..441614.43 rows=325302 loops=1) > -> Hash Join (cost=12439.25..12440.64 rows=1 width=85) >(actual time=103733.76..120916.86 rows=325302 loops=1) > -> Seq Scan on nation (cost=0.00..1.25 rows=25 >width=15) (actual time=7.81..8.72 rows=25 loops=1) > -> Hash (cost=12439.25..12439.25 rows=1 width=70) >(actual time=103722.25..103722.25 rows=0 loops=1) > -> Nested Loop (cost=0.00..12439.25 rows=1 >width=70) (actual time=95.43..100162.91 rows=325302 loops=1) > -> Nested Loop (cost=0.00..12436.23 >rows=1 width=62) (actual time=84.91..47502.93 rows=325302 loops=1) > -> Nested Loop >(cost=0.00..12412.93 rows=4 width=24) (actual time=66.86..8806.01 rows=43424 loops=1) > -> Seq Scan on part >(cost=0.00..12399.00 rows=1 width=4) (actual time=24.88..4076.81 rows=10856 loops=1) > -> Index Scan using >partsupp_pkey on partsupp (cost=0.00..13.89 rows=4 width=20) (actual time=0.20..0.34 >rows=4 loops=10856) > -> Index Scan using >l_partsupp_index on lineitem (cost=0.00..6.02 rows=1 width=38) (actual >time=0.20..0.61 rows=7 loops=43424) > -> Index Scan using supplier_pkey on >supplier (cost=0.00..3.01 rows=1 width=8) (actual time=0.08..0.10 rows=1 >loops=325302) > -> Index Scan using orders_pkey on orders >(cost=0.00..3.22 rows=1 width=8) (actual time=0.85..0.87 rows=1 loops=325302) > Total runtime: 505563.85 msec > > estimated 12000msec > > here is the query: > SELECT > nation, > o_year, > CAST((sum(amount))AS NUMERIC(10,2))AS sum_profit > FROM( > SELECT > nation.name AS nation, > EXTRACT(year FROM orders.orderdate) AS o_year, > lineitem.extendedprice*(1-lineitem.discount)-partsupp.supplycost*lineitem.quantity >AS amount > FROM > part, > supplier, > lineitem, > partsupp, > orders, > nation > WHERE > supplier.suppkey=lineitem.suppkey > AND partsupp.suppkey=lineitem.suppkey > AND partsupp.partkey=lineitem.partkey > AND part.partkey=lineitem.partkey > AND orders.orderkey=lineitem.orderkey > AND supplier.nationkey=nation.nationkey > AND part.name LIKE '%green%' > ) AS profit > GROUP BY > nation, > o_year > ORDER BY > nation, > o_year DESC; > > lineitem is about 6M rows > partsupp 800K rows > part 200K rows > > any advice? > Thanks and regards > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to find out if an index is unique?
Dirk, May be you can use this create view sesql_userindexes as SELECT upper(c.relname) AS TBL_NAME, upper(i.relname) AS IDX_NAME, CASE WHEN x.indisunique=false THEN 0 ELSE 1 END AS UNIQUE_FLAG, 1+ (CASE WHEN x.indkey[1]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[2]=0 THEN 0 ELSE 1 END)+ (CASE WHEN x.indkey[3]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[4]=0 THEN 0 ELSE 1 END)+ (CASE WHEN x.indkey[5]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[6]=0 THEN 0 ELSE 1 END)+ (CASE WHEN x.indkey[7]=0 THEN 0 ELSE 1 END) AS IDXCOL_TOTAL, x.indkey AS COL_SEQ FROM pg_index x, pg_class c, pg_class i WHERE ((c.oid = x.indrelid) AND (i.oid = x.indexrelid)) and not (c.relname ~* 'pg_') ; select * from sesql_userindexes order by tbl_name, idx_name ; gives you detailed information about all user-defined indices. Regards, Christoph > > is there a way to ask the system tables if a given index was created > with the unique qualification? I don't want to insert data to try. > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how do i import my sql query result to a file
not sure I understand the question, but from inside psql you can do: \o FILENAMEsend all query results to file or |pipe Robert Treat On Thu, 2002-07-18 at 17:47, Joseph Syjuco wrote: > how do i import results of my select query to a file > thanks > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Indexing UNIONs
Bruno, > My suggestion: > SELECT t3.id, coalesce(t1.name, t2.name), t1.abbreviation, > coalesce(t1.juris_id, t2.juris_id) from > (t3 left join t1 using (id)) left join t2 using (id); Cool! I didn't think of that. I'll give it a try. -Josh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how do i import my sql query result to a file
--- Joseph Syjuco <[EMAIL PROTECTED]> wrote: > how do i import results of my select query to a file > thanks > in the psql command prompt type \o and then type your select query. The result will be dumped into ludwig. __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.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
Re: [SQL] how do i import my sql query result to a file
thanks for the tips !!! actually i used \g . Again thanks On Thu, 2002-07-18 at 20:34, Ludwig Lim wrote: > > --- Joseph Syjuco <[EMAIL PROTECTED]> wrote: > > how do i import results of my select query to a file > > thanks > > > > in the psql command prompt type > \o > > and then type your select query. > The result will be dumped into > > ludwig. > > __ > Do You Yahoo!? > Yahoo! Autos - Get free new car price quotes > http://autos.yahoo.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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] id and ID in CREATE TABLE
Hello all, It might be not a correct place to post this. I am creating a table from psql. Everything is fine except I got some troubles when trying to create the same table but in a different way and with pgaccess. If I have this sql: CREATE TABLE ttt ( ID int2, name text ); from psql the ID comes into id in the table. The SQL statements work fine then. If I create the same table using pgaccess the table looks like: Table "ttt2" Column | Type | Modifiers +--+--- ID | smallint | name | text | After this if I INSERT and SELECT something the results are not the same anymore: TEST1=# INSERT INTO ttt2 VALUES (1,'l'); INSERT 17001 1 TEST1=# select * from ttt2; ID | name +--- 1 | l (1 row) TEST1=# select ID from ttt2; ERROR: Attribute 'id' not found Can somebody explain me a bit about: 1. As far as I know column names in Tables are not case sensitive. Correct ? So I know if I pickup ID is not a clever idea but for this example it is ok. As well if I have name and Name it should not matter for SQL. 2. Why psql converts from upper case in lower case column name ID ? Like in the first case. 3. How comes that first using psql I cannot create the column name ID but using pgaccess I can ? Is this a bug ? Some ideas ? stefan ---(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] id and ID in CREATE TABLE
> Can somebody explain me a bit about: > > 1. As far as I know column names in Tables are not case > sensitive. Correct ? > So I know if I pickup ID is not a clever idea but for this > example it is ok. I think your examples have proved that column names are in fact very much case sensitive. However, you will need to double quote mixed case names: eg. SELECT "ID" FROM ttt2; > As well if I have name and Name it should not matter for SQL. Well it does in Postgresql. I highly recommend you just use lowercase field names. > 2. Why psql converts from upper case in lower case column name ID ? > Like in the first case. Because you didn't double quote it. > 3. How comes that first using psql I cannot create the column name ID but > using pgaccess I can ? Is this a bug ? Because pgaccess is doing the double quoting for you. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] id and ID in CREATE TABLE
I forgot about "" Sorry. So if I would use names quoted then my questions are obsolete. Except one: So actually the only strange part would be PostgreSQL is folding to lower cases a column name ... >From docs: The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it. Why is like this ? Why not letting them upper case if they are not quoted ? stefan On Sat, 20 Jul 2002 [EMAIL PROTECTED] wrote: > > Hello all, > > It might be not a correct place to post this. I am creating a table from > psql. Everything is fine except I got some troubles when trying to create > the same table but in a different way and with pgaccess. > > If I have this sql: > > > CREATE TABLE ttt ( > > ID int2, > name text ); > > from psql the ID comes into id in the table. The SQL statements work fine > then. If I create the same table using pgaccess the table looks like: > > Table "ttt2" > Column | Type | Modifiers > +--+--- > ID | smallint | > name | text | > > > After this if I INSERT and SELECT something the results are not the same > anymore: > > TEST1=# INSERT INTO ttt2 VALUES (1,'l'); > INSERT 17001 1 > TEST1=# select * from ttt2; > ID | name > +--- > 1 | l > (1 row) > > TEST1=# select ID from ttt2; > ERROR: Attribute 'id' not found > > Can somebody explain me a bit about: > > 1. As far as I know column names in Tables are not case sensitive. Correct ? > So I know if I pickup ID is not a clever idea but for this example it is ok. > As well if I have name and Name it should not matter for SQL. > > 2. Why psql converts from upper case in lower case column name ID ? > Like in the first case. > > 3. How comes that first using psql I cannot create the column name ID but > using pgaccess I can ? Is this a bug ? > > > Some ideas ? > stefan > > > ---(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
