[SQL] Unique indexes not unique?
I found that Postgres isn't behaving like I thought when using a unique index in
combination with NULL-values...
Is this a bug or specified in the SQL-standard? If its a bug, is it fixed in a
recent version? We are using 7.2.3
This is the results I got:
intranet=# create table foo (a varchar(10), b varchar(10));
CREATE
intranet=# create unique index foo_idx on foo using btree(a, b);
CREATE
intranet=# insert into foo (a, b) values ('apa', 'banan');
INSERT 26229704 1
intranet=# insert into foo (a, b) values ('apa', 'banan');
ERROR: Cannot insert a duplicate key into unique index foo_idx
intranet=# insert into foo (a, b) values ('apa', null);
INSERT 26229706 1
intranet=# insert into foo (a, b) values ('apa', null);
INSERT 26229707 1
And another completely unrelated question... I have got a table with a composite
index on A andBb and an index on A
which I query with something like this:
SELECT * FROM table
WHERE (a = 1 OR a = 2 OR a = 3) AND b 1232132 AND b 123123123213123
Postgres then chooses to use the index for A three times, which is really slow
on my table...
Then I rewrote the query like:
SELECT * FROM table
WHERE a = 1 AND b 1232132 AND b 123123123213123
UNION SELECT * FROM table
WHERE a = 2 AND b 1232132 AND b 123123123213123
UNION SELECT * FROM table
WHERE a = 3 AND b 1232132 AND b 123123123213123
Postgres then behaved better and choosed the composite index in all three cases
resulting in a very large improvement...
Why is this, and has it been improved in more recent versions?
Thanks in advance,
Jimmy Mäkelä
Jimmy Mäkelä
Programmerare
Nybrogatan 55, Box 55708
114 83 Stockholm
Direkt: 08-527 90 457
Mobil: 073-623 05 51
Jag tycker att du borde anlita en agent.
Gå till: www.agent25.se
---(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] Unique indexes not unique?
Jimmy Mäkelä wrote:
I found that Postgres isn't behaving like I thought when using a
unique index in
combination with NULL-values...
Is this a bug or specified in the SQL-standard? If its a bug, is it
fixed in a
recent version? We are using 7.2.3
This is the results I got:
intranet=# create table foo (a varchar(10), b varchar(10));
CREATE
intranet=# create unique index foo_idx on foo using btree(a, b);
CREATE
intranet=# insert into foo (a, b) values ('apa', 'banan');
INSERT 26229704 1
intranet=# insert into foo (a, b) values ('apa', 'banan');
ERROR: Cannot insert a duplicate key into unique index foo_idx
intranet=# insert into foo (a, b) values ('apa', null);
INSERT 26229706 1
intranet=# insert into foo (a, b) values ('apa', null);
INSERT 26229707 1
I'm not sure unique index works properly for null values. I can't
explain, why. Maybe it comes from SQL standard - null i a special value
and can't be compared using default operators to other non null values:
1null =null
1null =null
1=null =null
And another completely unrelated question... I have got a table with a
composite
index on A andBb and an index on A
which I query with something like this:
SELECT * FROM table
WHERE (a = 1 OR a = 2 OR a = 3) AND b 1232132 AND b 123123123213123
Postgres then chooses to use the index for A three times, which is
really slow
on my table...
Then I rewrote the query like:
SELECT * FROM table
WHERE a = 1 AND b 1232132 AND b 123123123213123
UNION SELECT * FROM table
WHERE a = 2 AND b 1232132 AND b 123123123213123
UNION SELECT * FROM table
WHERE a = 3 AND b 1232132 AND b 123123123213123
Try to rewrite your query to show postgres how to use index on AB:
SELECT * FROM table
WHERE
(a = 1 AND b 1232132 AND b 123123123213123) or
(a = 2 AND b 1232132 AND b 123123123213123) or
(a = 3 AND b 1232132 AND b 123123123213123);
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Unique indexes not unique?
From: Tomasz Myrta [mailto:[EMAIL PROTECTED]] I'm not sure unique index works properly for null values. I can't explain, why. Maybe it comes from SQL standard - null i a special value Yeah, I thought about that too, but I think that behaviour is really bad and would consider it a bug. There are good reasons for having a special SQL null, but none of these apply to unique indexes (not that I can think of anyway). Try to rewrite your query to show postgres how to use index on AB: SELECT * FROM table WHERE (a = 1 AND b 1232132 AND b 123123123213123) or (a = 2 AND b 1232132 AND b 123123123213123) or (a = 3 AND b 1232132 AND b 123123123213123); Sure, this works, and is an improvement to the UNION-version, but I think postgres should be able do these substitutions by itself in the planner/optimizer... Or is there any method for specifying optimizer hints? Regards, Jimmy ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Unique indexes not unique?
On Mon, 13 Jan 2003, [iso-8859-1] Jimmy Mäkelä wrote: I found that Postgres isn't behaving like I thought when using a unique index in combination with NULL-values... Is this a bug or specified in the SQL-standard? If its a bug, is it fixed in a recent version? We are using 7.2.3 AFAIK this is standard. From the unique predicate (8.9), If there are no two rows in T such that the value of each column in one row is non-null and is equal to the value of the corresponding column in the other row according to Subclause 8.2, comparison predicate, then the result of the unique predicate is true; otherwise, the result of the unique predicate is false. Unique constraints are defined in terms of the unique predicate. And another completely unrelated question... I have got a table with a composite index on A andBb and an index on A which I query with something like this: SELECT * FROM table WHERE (a = 1 OR a = 2 OR a = 3) AND b 1232132 AND b 123123123213123 Postgres then chooses to use the index for A three times, which is really slow on my table... On my dev (7.4devel) box I see it using the composite index three times, but you haven't given explain output for the two queries or any statistics information so that doesn't say much. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Unique indexes not unique?
Jimmy Mäkelä wrote:
I found that Postgres isn't behaving like I thought when using a
unique index in
combination with NULL-values...
Is this a bug or specified in the SQL-standard? If its a bug, is it
fixed in a
recent version? We are using 7.2.3
intranet=# insert into foo (a, b) values ('apa', null);
INSERT 26229706 1
intranet=# insert into foo (a, b) values ('apa', null);
INSERT 26229707 1
I'm not sure unique index works properly for null values. I can't
explain, why. Maybe it comes from SQL standard - null i a special value
and can't be compared using default operators to other non null values:
1null =null
1null =null
1=null =null
Null is not a value or even a special value, it is supposed to represent
the absence of a value. It means either not applicable or not known.
It doesn't make sense to say whether one null is the same as another, a
null is an absence, a hole. As a result, you can't really talk about
comparing two nulls, only testing whether a value is null.
If you are using a null in a situation where it should be unique, you
probably want a value instead. Can't say more without an actual example.
- Richard Huxton
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Crosstab-style query in pure SQL
TIA all I have a table containing milestones achieved for projects, marked as being in particular quarters. CREATE TABLE milestones ( proj_id int4, sortorder SERIAL, qtr int4, description varchar(200) ); Now I need the milestone descriptions output across the page like: proj_id | q1 | q2 | q3 | q4 and sorted according to sortorder. Judicious use of CASE can get me the quarterly columns but I need to eliminate holes. Any clever ideas without resorting to procedural solutions (either table function or application code)? TIA - Richard Huxton ---(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] Unique indexes not unique?
Stephan Szabo [EMAIL PROTECTED] writes: On Mon, 13 Jan 2003, [iso-8859-1] Jimmy Mäkelä wrote: And another completely unrelated question... I have got a table with a composite index on A andBb and an index on A which I query with something like this: SELECT * FROM table WHERE (a = 1 OR a = 2 OR a = 3) AND b 1232132 AND b 123123123213123 Postgres then chooses to use the index for A three times, which is really slow on my table... On my dev (7.4devel) box I see it using the composite index three times, but you haven't given explain output for the two queries or any statistics information so that doesn't say much. [ checks CVS logs... ] I believe 7.2 should behave the same; the relevant change predated 7.2: 2001-06-05 13:13 tgl * src/: backend/optimizer/path/allpaths.c, backend/optimizer/path/indxpath.c, include/optimizer/paths.h, backend/optimizer/path/orindxpath.c: Improve planning of OR indexscan plans: for quals likeWHERE (a = 1 or a = 2) and b = 42 and an index on (a,b), include the clause b = 42 in the indexquals generated for each arm of the OR clause. Essentially this is an index- driven conversion from CNF to DNF. Implementation is a bit klugy, but better than not exploiting the extra quals at all ... There may be a datatype coercion issue: in the example as quoted, '123123123213123' is a bigint constant. If b is int then that comparison wouldn't be considered indexable (and if it's bigint, then the other comparison against b wouldn't be indexable without adding a cast). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Unique indexes not unique?
On Mon, 13 Jan 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On Mon, 13 Jan 2003, [iso-8859-1] Jimmy Mäkelä wrote: On my dev (7.4devel) box I see it using the composite index three times, but you haven't given explain output for the two queries or any statistics information so that doesn't say much. [ checks CVS logs... ] I believe 7.2 should behave the same; the relevant change predated 7.2: 2001-06-05 13:13 tgl * src/: backend/optimizer/path/allpaths.c, backend/optimizer/path/indxpath.c, include/optimizer/paths.h, backend/optimizer/path/orindxpath.c: Improve planning of OR indexscan plans: for quals likeWHERE (a = 1 or a = 2) and b = 42 and an index on (a,b), include the clause b = 42 in the indexquals generated for each arm of the OR clause. Essentially this is an index- driven conversion from CNF to DNF. Implementation is a bit klugy, but better than not exploiting the extra quals at all ... There may be a datatype coercion issue: in the example as quoted, '123123123213123' is a bigint constant. If b is int then that comparison wouldn't be considered indexable (and if it's bigint, then the other comparison against b wouldn't be indexable without adding a cast). In his actual query (he sent me explain results which include the query) he uses ::bigint on both constants. -- Quoting the explain section from his message -- EXPLAIN SELECT * FROM agentresults WHERE (usr = 'svt' OR usr = 'svt1' OR usr = 'svt2') AND modified = 1042239600::bigint AND modified 1042498800::bigint AND category != '' AND (flags 16) 0 AND title != 'a25uniq' ORDER BY modified DESC LIMIT 1000; returns Limit (cost=607870.16..607870.16 rows=94 width=372) - Sort (cost=607870.16..607870.16 rows=95 width=372) - Index Scan using agentresults2_usr, agentresults2_usr, agentresults2_usr on agentresults (cost=0.00..607867.04 rows=95 width=372) EXPLAIN SELECT * FROM agentresults WHERE (usr = 'svt' AND modified = 1042239600::bigint AND modified 1042498800::bigint AND category != '' AND (flags 16) 0 AND title != 'a25uniq') OR (usr = 'svt1' AND modified = 1042239600::bigint AND modified 1042498800::bigint AND category != '' AND (flags 16) 0 AND title != 'a25uniq') OR (usr = 'svt2' AND modified = 1042239600::bigint AND modified 1042498800::bigint AND category != '' AND (flags 16) 0 AND title != 'a25uniq') ORDER BY modified DESC LIMIT 1000; returns Limit (cost=22669.68..22669.68 rows=95 width=372) - Sort (cost=22669.68..22669.68 rows=96 width=372) - Index Scan using agentresults2_modified_user, agentresults2_modified_user, agentresults2_modified_user on agentresults (cost=0.00..22666.52 rows=96 width=372) -- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Unique indexes not unique?
Stephan Szabo [EMAIL PROTECTED] writes: In his actual query (he sent me explain results which include the query) he uses ::bigint on both constants. Okay, scratch that theory. Limit (cost=22669.68..22669.68 rows=95 width=372) - Sort (cost=22669.68..22669.68 rows=96 width=372) - Index Scan using agentresults2_modified_user, agentresults2_modified_user, agentresults2_modified_user on agentresults (cost=0.00..22666.52 rows=96 width=372) Should I guess from the index name that it is on (modified, usr) and not on (usr, modified)? If so, the problem is that the OR-expansion code only triggers if it has found an OR-clause that's already usable with the index --- ie, matches the index's first column. So this index is the wrong way 'round for ... WHERE (usr = 'svt' OR usr = 'svt1' OR usr = 'svt2') AND modified = 1042239600::bigint AND modified 1042498800::bigint ... It would be nice someday for the expansion to work in the other case too, but I haven't thought of a way to do it that would not waste many cycles in typical queries where there is no benefit from searching for OR-clauses. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Crosstab-style query in pure SQL
Richard, I have a table containing milestones achieved for projects, marked as being in particular quarters. CREATE TABLE milestones ( proj_id int4, sortorder SERIAL, qtr int4, description varchar(200) ); Now I need the milestone descriptions output across the page like: proj_id | q1 | q2 | q3 | q4 and sorted according to sortorder. Ah! A classic SQL problem. Take a look at Joe Celko's SQL for Smarties: he does a good job of defining and discussing the three different solutions to the Crosstab Query problem. A second method you can use is the subselect method: SELECT proj_id, sortorder, qart1.q1, quart2.q2 FROM milestones LEFT OUTER JOIN ( SELECT proj_id, description as q1 FROM milestones WHERE qtr = 1) quart1 ON quart1.proj_id = milestones.proj_id LEFT OUTER JOIN ( SELECT proj_id, description q2 ... However, the above is generally chosen over the CASE statement method when the crosstab involves multiple tables; in your case, it is not a performance or similicity gain. There is a third method which involves building a grid of values: ref qtr1qtr2qtr3qtr4 qtr11 0 0 0 qtr20 1 0 0 qtr30 0 1 0 qtr40 0 0 1 This method is the only one of the three that prevents you from having to build your query using procedural logic for a dynamic set of parameters. However, the above is set up for numeric values (i.e. one multiplies the grid number by the value to add into calculations) and will not work for your varchar-returning query. You could, however, constuct the grid with NULLs and '' strings and use that to modify the value; I leave that to your ingenuity. -Josh Berkus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] PgSQL 7.3: /opt/pgsql/bin/postgres: Execute permission denied.
Hi, I download PgSQL source, compiled it, then try to start pgsql, I got: # su postgres -c '/opt/pgsql/bin/initdb --pgdata=/var/opt/pgsql/data' The program '/opt/pgsql/bin/postgres' needed by initdb does not belong to PostgreSQL version 7.3, or there may be a configuration problem. This was the error message issued by that program: /opt/pgsql/bin/initdb[135]: /opt/pgsql/bin/postgres: Execute permission denied. I check the permission of postgres: # ls -l /opt/pgsql/bin/postgres -rwxr-xr-x 1 root bin2994176 Jan 8 09:53 /opt/pgsql/bin/postgres But same code works fine on my another HPUX 11.11 system. File permission is same. Does anyone have some ideas? Thanks! Zengfa __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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] Postgresql Bug List?
Is there a bugzilla kind of thing for Postgresql? I would like to help out on the development, but have no idea where to start... Thanks Wei ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] Returning row or rows from function?
I want to create a function that will return a row or rows of a table is this possible? If so can someone replay with a complete example? ---(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 confusion
I'm confused. How do I get the integer 10102 to come in as the date
2002-01-01?
cmi= select to_date('010102','MMDDYY');
to_date
2002-01-01
(1 row)
cmi= select to_char(10102,'00');
to_char
-
010102
(1 row)
cmi= select to_date(to_char(10102,'00'),'MMDDYY');
to_date
2010-01-10
(1 row)
TIA!
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Inherancing
You have to insert in th centers-table. Because it is inherited from cities, the record is automatically in cities. Search for SELECT * FROM ONLY in the docs too. create table cities (id int, name varchar, primary key(id) ); create table centers (state varchar(2)) inherits (cities); ant the tuple insert into cities values (1, 'Lajeado'); Lajeado isn't a center here. You should do : insert into centers You can do then SELECT * FROM cities - you will get all cities. Or SELECT * FROM centers - you will get all centers. Or SELECT * FROM ONLY cities - you will get only cities who aren't centers. Daniel. Nasair Junior da Silva [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Hi people, supposing i have the tables : create table cities (id int, name varchar, primary key(id) ); create table centers (state varchar(2)) inherits (cities); ant the tuple insert into cities values (1, 'Lajeado'); How i do if i want to make this city as a center ? Thanks, xx===xx || °v° Nasair Junior da Silva || || /(_)\ Linux User: 246054 || || ^ ^ [EMAIL PROTECTED]|| ||CPD - Desenvolvimento || ||Univates - Centro Universitário|| xx===xx ---(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] to_date confusion
Richard, I'm confused. How do I get the integer 10102 to come in as the date 2002-01-01? Hmmm ... isn't this an old post, repeating? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Returning row or rows from function?
On Wed, 8 Jan 2003, David Durst wrote: I want to create a function that will return a row or rows of a table is this possible? It is in 7.3. If so can someone replay with a complete example? You can find some examples in: http://techdocs.postgresql.org/guides/SetReturningFunctions ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Returning row or rows from function?
David Durst wrote: I want to create a function that will return a row or rows of a table is this possible? If so can someone replay with a complete example? I propose you reading a document of Stephan Szabo about functions returning sets. You need postgresql 7.3 to do this. http://techdocs.postgresql.org/guides/SetReturningFunctions Tomasz Myrta ---(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 confusion
On Mon, 13 Jan 2003, Josh Berkus wrote: Richard, I'm confused. How do I get the integer 10102 to come in as the date 2002-01-01? Hmmm ... isn't this an old post, repeating? Yep, my guess is that he sent it, wasn't on the list so it went for approval, he joined and resent, and we're now getting the approved version of it (given that I just received a bunch of messages with dates about a week ago) ---(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] Crosstab-style query in pure SQL
Richard, I have a table containing milestones achieved for projects, marked as being in particular quarters. CREATE TABLE milestones ( proj_id int4, sortorder SERIAL, qtr int4, description varchar(200) ); Now I need the milestone descriptions output across the page like: proj_id | q1 | q2 | q3 | q4 and sorted according to sortorder. Ah! A classic SQL problem. Take a look at Joe Celko's SQL for Smarties: he does a good job of defining and discussing the three different solutions to the Crosstab Query problem. A second method you can use is the subselect method: SELECT proj_id, sortorder, qart1.q1, quart2.q2 FROM milestones LEFT OUTER JOIN ( SELECT proj_id, description as q1 FROM milestones WHERE qtr = 1) quart1 ON quart1.proj_id = milestones.proj_id LEFT OUTER JOIN ( SELECT proj_id, description q2 ... However, the above is generally chosen over the CASE statement method when the crosstab involves multiple tables; in your case, it is not a performance or similicity gain. Thanks Josh, but that still leaves me with nulls if I join on sortorder too, and duplicates if not (as you imply, since it's equivalent to the CASE option). The problem is that since I defined sortorder as a serial, equivalent rows of a specific project don't match across the key. Without calculating a row_index based on (proj_id,sortorder) it doesn't look like there's anything to be done without procedural help. Didn't think there was anything simple - my fault for not having common keys to match up output rows - oversimplified the input stage and I'm paying for it on output. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PgSQL 7.3: /opt/pgsql/bin/postgres: Execute permission denied.
Try running the initdb program first. Best Wishes, Chris Travers - Original Message - From: Zengfa Gao [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 9:43 AM Subject: [SQL] PgSQL 7.3: /opt/pgsql/bin/postgres: Execute permission denied. Hi, I download PgSQL source, compiled it, then try to start pgsql, I got: # su postgres -c '/opt/pgsql/bin/initdb --pgdata=/var/opt/pgsql/data' The program '/opt/pgsql/bin/postgres' needed by initdb does not belong to PostgreSQL version 7.3, or there may be a configuration problem. This was the error message issued by that program: /opt/pgsql/bin/initdb[135]: /opt/pgsql/bin/postgres: Execute permission denied. I check the permission of postgres: # ls -l /opt/pgsql/bin/postgres -rwxr-xr-x 1 root bin2994176 Jan 8 09:53 /opt/pgsql/bin/postgres But same code works fine on my another HPUX 11.11 system. File permission is same. Does anyone have some ideas? Thanks! Zengfa __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] assigning values to array elements
Hello, I'm fairly new to postgres and I'm trying to write a PL/pgsql function and keep getting an error message. Here is the line that is giving me trouble: norm[i] := (NEW.area[i]/(A_slope * (i+18) + A_int)+NEW.area[i]/(C_slope * (i+18) + C_int))/2; The error message that I receive is: WARNING: plpgsql: ERROR during compile of norm_n_scan near line 27 ERROR: parse error at or near [ As you can see, I'm trying to assign a value to an array element, but it isn't working for some reason. FYI, norm is declared as a floating point array of unlimited size. Please let me know if you have any suggestions. Thanks!! -Jay Greenbaum P.S. My apologies for posting this to 2 lists! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] assigning values to array elements
J Greenbaum [EMAIL PROTECTED] writes: As you can see, I'm trying to assign a value to an array element, but it isn't working for some reason. plpgsql doesn't support that yet :-(. This needs to be fixed. I'm not sure why it doesn't have an entry in TODO. Bruce, would you add something like * allow assignment to array elements to the plpgsql section? 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] assigning values to array elements
Darn...guess I gotta do it in perl then. Thx for your help. On Mon, 13 Jan 2003, Tom Lane wrote: J Greenbaum [EMAIL PROTECTED] writes: As you can see, I'm trying to assign a value to an array element, but it isn't working for some reason. plpgsql doesn't support that yet :-(. This needs to be fixed. I'm not sure why it doesn't have an entry in TODO. Bruce, would you add something like * allow assignment to array elements to the plpgsql section? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] query speed joining tables
I have4 tables that I need to query... 3 of the tables are links by the field userid. below are table sql fragments. as well as the query.The subquery causes very high CPU usages. It typically returns ~3000 matches.Is there another way to rewrite this? SELECT user_login.userid FROM user_login,user_details_p,user_match_detailsWHERE user_login.userid = user_details_p.userid ANDuser_details_p.userid = user_match_details.userid ANDuser_details_p.gender ='W' ANDuser_details_p.seekgender ='M' ANDuser_details_p.age =18 AND user_details_p.age =50 ANDuser_match_details.min_age = 30 ANDuser_match_details.max_age = 30 ANDuser_details_p.ethnictype = 'Caucasian (White)' ANDstrpos(user_match_details.ethnicity,'Asian') !=0 ANDuser_details_p.zipcode in (select zips_max.destination from zips_max where zips_max.origin='90210' )order by user_login.last_login desc; Table "public.user_login" Column | Type | Modifiers +--+---userid | character varying(30) | not nullpassword | character varying(30) | not nullemail | character varying(50) | not nulllast_login | timestamp with time zone | not nullIndexes: user_login_pkey primary key btree (userid), Table "public.user_details_p" Column | Type | Modifiers -+--+---userid | character varying(30) | not nullgender | character varying(1) | age | integer | height | character varying(10) | ethnicty pe | character varying(30) | education | character varying(30) | createdate | timestamp with time zone | default now()zipcode | character varying(5) | birthdate | date | default now()zodiac | character varying(40) | seekgender | character varying(2) | Indexes: user_details_p_pkey primary key btree (userid), user_details_p_age_idx btree (age), sp; user_details_p_ethnic_idx btree (ethnictype), user_details_p_gender_idx btree (gender), user_details_p_last_login_idx btree (last_login), user_details_p_seekgender_idx btree (seekgender), user_details_p_state_idx btree (state)Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTION ON DELETE CASCADE Table "public.user_match_details" Column | Type | Modifiers --++---userid | character varying(30) | not nullsoughtmate | character varying(200) | ethnicity | character varying(200) | marital_status | character varying(200) | min_age | integer | max_age | integer p; | city | character varying(50) | state | character varying(2) | zipcode | integer | match_distance | integer | Indexes: user_match_details_pkey primary key btree (userid)Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTION ON DELETE CASCADE Table "public.zips_300" Column | Type | Modifiers -+--+---origin | character varying(5) | destination | character varying(5) | Indexes: zips_300_origin_idx btree (origin) Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Re: [SQL] query speed joining tables
Chris, Here are probably your two main query problems: strpos(user_match_details.ethnicity,'Asian') !=0 AND It is impossible for Postgres to use an index for the above as it is written. I would suggest one of two improvements; 1) Modify the schema so that multiple ethnicity details are kept in a sub-table rather than a free-form text field you have to search, or: 2) Create a cachable function for contains_asian and index on that. user_details_p.zipcode in (select zips_max.destination from zips_max where zips_max.origin='90210' ) order by user_login.last_login desc; Use a WHERE EXISTS clause instead of IN. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] query speed joining tables
Iunderstand cachable functions but your proposed application is a little unclear. is it possible to see an example? thanks in advance. Josh Berkus [EMAIL PROTECTED] wrote: Chris,Here are probably your two main query problems: strpos(user_match_details.ethnicity,'Asian') !=0 ANDIt is impossible for Postgres to use an index for the above as it is written. I would suggest one of two improvements;1) Modify the schema so that multiple ethnicity details are kept in a sub-table rather than a free-form text field you have to search, or:2) Create a cachable function for "contains_asian" and index on that. user_details_p.zipcode in (select zips_max.destination from zips_max where zips_max.origin='90210' ) order by user_login.last_login desc;Use a "WHERE EXISTS" clause instead of "IN". -- -Josh BerkusAglio Database SolutionsSan Francisco---(end of broadcast)---TIP 3: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanlyDo you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Re: [SQL] query speed joining tables
Christopher Smith wrote: I have 4 tables that I need to query... 3 of the tables are links by the field userid. below are table sql fragments. as well as the query. The subquery causes very high CPU usages. It typically returns ~3000 matches. Is there another way to rewrite this? SELECT user_login.userid FROM user_login,user_details_p,user_match_details WHERE user_login.userid = user_details_p.userid AND user_details_p.userid = user_match_details.userid AND user_details_p.gender ='W' AND user_details_p.seekgender ='M' AND user_details_p.age =18 AND user_details_p.age =50 AND user_match_details.min_age = 30 AND user_match_details.max_age = 30 AND user_details_p.ethnictype = 'Caucasian (White)' AND strpos(user_match_details.ethnicity,'Asian') !=0 AND user_details_p.zipcode in (select zips_max.destination from zips_max where zips_max.origin='90210' ) order by user_login.last_login desc; explicit joins show better idea of your query and helps postgres choosing indexing. select userid from user_login join user_details using (userid) join user_match_details using (userid) where user_details_p.gender ='W' AND user_details_p.seekgender ='M' AND user_details_p.age between 18 and 50 and user_match_details.min_age = 30 AND user_match_details.max_age = 30 AND user_details_p.ethnictype = 'Caucasian (White)' AND strpos(user_match_details.ethnicity,'Asian') !=0 AND user_details_p.zipcode in (select zips_max.destination from zips_max where zips_max.origin='90210' ) order by user_login.last_login desc; How can I help you with subquery if you didn't write even zips_max definition?!? If origin is unique value in that table, you can change subquery into join on from list. Table public.user_login Column | Type | Modifiers +--+--- userid | character varying(30)| not null password | character varying(30)| not null email | character varying(50)| not null last_login | timestamp with time zone | not null Indexes: user_login_pkey primary key btree (userid), Do you really need userid as varchar? indexing on int4 or int8 would be much faster than varchar Why do you have 3 tables? It looks like only one table would be enough. Remember, that null values don't take too much space. Table public.user_details_p Column | Type | Modifiers -+--+--- userid | character varying(30)| not null gender | character varying(1) | age | integer | height | character varying(10)| ethnicty pe | character varying(30)| education | character varying(30)| createdate | timestamp with time zone | default now() zipcode | character varying(5) | birthdate | date | default now() zodiac | character varying(40)| seekgender | character varying(2) | Indexes: user_details_p_pkey primary key btree (userid), user_details_p_age_idx btree (age), nb sp; user_details_p_ethnic_idx btree (ethnictype), user_details_p_gender_idx btree (gender), user_details_p_last_login_idx btree (last_login), user_details_p_seekgender_idx btree (seekgender), user_details_p_state_idx btree (state) There is too many indexes - index on gender (2 possible values) is useless, index on ethnic (how many values - I think not too many?) is possibly useless Consider creating single index on several fields for queries like this: select ... where user_details_p.gender ='W' AND user_details_p.seekgender ='M' AND user_details_p.age between 18 and 50 and index on (age,seekgender,gender) Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTI ON ON DELETE CASCADE Table public.user_match_details Column | Type | Modifiers --++--- userid | character varying(30) | not null soughtmate | character varying(200) | ethnicity| character varying(200) | marital_status | character varying(200) | min_age | integer| max_age | integer nbs p;| city | character varying(50) | state| character varying(2) | zipcode | integer| match_distance | integer| Indexes: user_match_details_pkey primary key btree (userid) Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTION ON DELETE CASCADE Table public.zips_300 Column| Type | Modifiers -+--+--- origin | character varying(5) | destination | character varying(5) | Indexes: zips_300_origin_idx btree (origin)
Re: [SQL] query speed joining tables
my mistakes, zips_max should be zips_300. Tomasz Myrta [EMAIL PROTECTED] wrote: Christopher Smith wrote: I have 4 tables that I need to query... 3 of the tables are links by the field userid. below are table sql fragments. as well as the query. The subquery causes very high CPU usages. It typically returns ~3000 matches. Is there another way to rewrite this? SELECT user_login.userid FROM user_login,user_details_p,user_match_details WHERE user_login.userid = user_details_p.userid AND user_details_p.userid = user_match_details.userid AND user_details_p.gender ='W' AND user_details_p.seekgender ='M' AND user_details_p.age =18 AND user_details_p.age =50 AND user_match_details.min_age = 30 AND user_match_details.max_age = 30 AND user_details_p.ethnictype = 'Caucasian (White)' AND strpos(user_matc h_details.ethnicity,'Asian') !=0 AND user_details_p.zipcode in (select zips_max.destination from zips_max where zips_max.origin='90210' ) order by user_login.last_login desc;explicit joins show better idea of your query and helps postgres choosing indexing.select useridfromuser_loginjoin user_details using (userid)join user_match_details using (userid)whereuser_details_p.gender ='W' ANDuser_details_p.seekgender ='M' ANDuser_details_p.age between 18 and 50 anduser_match_details.min_age = 30 ANDuser_match_details.max_age = 30 ANDuser_details_p.ethnictype = 'Caucasian (White)' ANDstrpos(user_match_details.ethnicity,'Asian') !=0 ANDuser_details_p.zipcode in (select zips_max.destination from zips_maxwhere zips_max.origin='90210' )order by user_login.last_login desc;How can I help you with subquery if you didn't write even zips_max definition?!?If origin is unique value in that table, you can change subquery into join on "from" list. Table "public.user_login" Column | Type | Modifiers +--+--- userid | character varying(30) | not null password | character varying(30) | not null email | character varying(50) | not null last_login | timestamp with time zone | not null Indexes: user_login_pkey primary key btree (userid),Do you really need userid as varchar?indexing on int4 or int8 would be much faster than varcharWhy do you have 3 tables? It looks like only one table would be enough. Remember, that null values don't take too much space. Table "public.user_details_p" Column | Type | Modifiers -+--+--- userid | character varying(30) | not null gender | character varying(1) | age | integer | height | character varying(10) | ethnicty pe | character varying(30) | education | character varying(30) | createdate | timestamp with time zone | default now() zipcode | character varying(5) | birthdate | date | default now() zodiac | character varying(40) | seekgender | character varying(2) | Indexes: user_details_p_pkey primary key btree (userid), user_details_p_age_idx btree (age), nb sp; user_details_p_ethnic_idx btree (ethnictype), user_details_p_gender_idx btree (gender), user_details_p_last_login_idx btree (last_login), user_details_p_seekgender_idx btree (seekgender), user_details_p_state_idx btree (state)There is too many indexes -index on gender (2 possible values) is useless,index on ethnic (how many values - I think not too many?) is possibly uselessCon sider creating single index on several fields for queries like this:select...whereuser_details_p.gender ='W' ANDuser_details_p.seekgender ='M' ANDuser_details_p.age between 18 and 50 andindex on (age,seekgender,gender) Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTI ON ON DELETE CASCADE Table "public.user_match_details" Column | Type | Modifiers --++--- userid | character varying(30) | not null soughtmate | character varying(200) | ethnicity | character varying(200) | marital_status | character varying(200) | min_age | integer | max_age | integer nbs p; | city | character varying(50) | state | character varying(2) | zipcode | integer | match_distance | integer | Indexes: user_match_ details_pkey primary key btree (userid) Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTION ON DELETE CASCADE Table "public.zips_300" Column | Type | Modifiers -+--+--- origin | character varying(5) | destination | character varying(5) | Indexes: zips_300_origin_idx btree (origin)If you need more specific answer, you have to add more information - how many records do you have in your tables and how many possible values do you use for example for zipcodes, ethnicity etc.Regards,Tomasz MyrtaDo you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Re: [SQL] insert rule doesn't see id field
On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote:
Ron Peterson [EMAIL PROTECTED] writes:
CREATE RULE person_insert AS
ON INSERT TO person
DO
INSERT INTO person_log ( name_last, name_first, mod_type, person_id )
VALUES ( new.name_last, new.name_first, 'I', new.id );
[where id is a serial column]
My insert rule creates a record in person_log just fine. It inserts
values for all of the fields except person_id. Why doesn't new.id
contain a value?
This is a bug in 7.2.*. It's fixed in 7.3. However, your rule will
still not work the way you would like, because rules are macros: the
default expression for id will get evaluated once in the rule and once
in your original query, leading to two different sequence numbers
getting inserted.
I just installed 7.3.1. It works now, as you say, but it breaks if the
id field being updated by an insert rule references the id field it's
logging. And like you say, the rule also updates the sequence - not a
killer, but not so great.
I started writing a trigger. Meanwhile I'm just going to log updates
and deletes. After all, if a record has never been updated or deleted,
what's to audit?
One thing's tripping me up a bit while writing a trigger (in C - I like
C). I'd like the trigger function arguments to specify an attribute
mapping from the table being logged to the log table - e.g. logfun
('logtable', 'oldatt1', 'newatt1', 'oldatt2', 'newatt2', etc.). I
thought I'd be good and check that the types and field lengths match
before attempting to insert the log record. I can find out this info
for the relation pulling the trigger easy enought, but how would I go
about getting this info when all I have is the table/field name? I
could create and execute a SQL query something like the following, but
is that really the way to get at this info in C code?
select
relname,
attname,
typname,
typlen
from
pg_class c,
pg_attribute a,
pg_type t
where
c.oid=a.attrelid and
a.atttypid=t.oid and
get just the relevant record(s);
--
Ron Peterson -o)
Network Systems Manager /\\
Mount Holyoke College_\_v
http://www.mtholyoke.edu/~rpeterso
---(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] insert rule doesn't see id field
Ron Peterson [EMAIL PROTECTED] writes: I can find out this info for the relation pulling the trigger easy enought, but how would I go about getting this info when all I have is the table/field name? I could create and execute a SQL query something like the following, but is that really the way to get at this info in C code? Open the target relation and examine its tuple descriptor ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Postgresql Bug List?
Go to the developer's site, and read the developer's FAQ, or see FAQ_DEV in the distribution. --- Wei Weng wrote: Is there a bugzilla kind of thing for Postgresql? I would like to help out on the development, but have no idea where to start... Thanks Wei ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] assigning values to array elements
Tom Lane wrote: J Greenbaum [EMAIL PROTECTED] writes: As you can see, I'm trying to assign a value to an array element, but it isn't working for some reason. plpgsql doesn't support that yet :-(. This needs to be fixed. I'm not sure why it doesn't have an entry in TODO. Bruce, would you add something like * allow assignment to array elements to the plpgsql section? Added to TODO: o Allow PL/PgSQL to support array element assignment -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query speed joining tables
Christopher Smith wrote: my mistakes, zips_max should be zips_300. and in my zip code table there are 120 million rows, example of the records are origin destination === 90210 90222 90210 90234 90210 96753 1.try to create index on both fields on zips_300 - origin and destination zips_300_ind(origin,destination) 2.if you have only unique pairs in zips_300, this query should noticable speed up you example: select userid from user_login UL join user_details_p UD using (userid) join user_match_details UM using (userid) join zips_300 Z on (Z.destination=UM.zipcode and Z.origin='90210') where UD.gender ='W' AND UD.seekgender ='M' AND UD.age between 18 and 50 and UMD.min_age = 30 AND UMD.max_age = 30 AND UD.ethnictype = 'Caucasian (White)' AND strpos(UMD.ethnicity,'Asian') !=0 order by user_login.last_login desc; Next step to speed up your query is answering such question: - How many values do I get if I ask one question. Example: gender='W' - 50% rows seekgender='M' - 50% rows ethnictype='Caucasian (White)' - 5% Start indexing your tables on smallest values - in this situation - ethnictype. Consider using multi-column indexes. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
