Re: [SQL] Trigger/Sequence headache
Hi, Stephen, Foster, Stephen wrote: > That's what I thought was going to be the answer. I was just hoping I > was making a mistake somehow. It's no big deal but I like things > organized and hate giant holes. > > Ok, one more thing for one of the batch jobs. No problem I have a > cleanup routine. Out of curiosity: Could you explain what's the problem with the holes? Bigserial should provide enough number space that holes are no problem. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Slow update SQL
On Mon, 2006-02-13 at 22:17 -0700, Michael Fuhr wrote:
[Please copy the mailing list on replies.]
On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:
> On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:
> > How many rows does the condition match?
>
> csalgorithm=# SELECT count(*) FROM ncccr10 WHERE
> date_part('year',dxdate) > '2000';
> count
>
> 199209
> (1 row)
You're updating about a third of the table; an _expression_ index on
date_part probably wouldn't help because the planner is likely to
stick with a sequential scan for such a large update. Even if it
did help it's likely to be a small fraction of the total time.
The table definition you sent me showed nine indexes. You might
see a substantial performance improvement by dropping all the
indexes, doing the update, then creating the indexes again (don't
forget to vacuum and analyze the table after the update). However,
dropping the indexes has obvious implications for other queries so
you might need to do the update at a time when that doesn't matter.
> > Have you queried pg_locks
> > to see if the update is blocked on an ungranted lock?
>
> I don't know what that is. How do I query pg_locks?
SELECT * FROM pg_locks;
http://www.postgresql.org/docs/7.4/static/monitoring-locks.html
> > What version of PostgreSQL are you running?
>
> 7.4.8. Thank you for your help.
Newer versions generally perform better; consider upgrading to 8.0
or 8.1 if possible.
Removing the indexes, running the update SQL, and then adding back the indexes worked much faster. Thank you for you help.
Re: [SQL] Slow update SQL
Hi, Ken, Ken Hill wrote: > Removing the indexes, running the update SQL, and then adding back the > indexes worked much faster. Thank you for you help. It might be a good idea to run VACUUM FULL between updating and reindexing. If you want to CLUSTER on an index, it will be best to create this index first, then CLUSTER the table, and then recreate the other indices. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Non Matching Records in Two Tables
On Wed, 2006-02-08 at 22:31 +0100, Markus Schaber wrote: Hi, Ken, Ken Hill schrieb: > I need some help with a bit of SQL. I have two tables. I want to find > records in one table that don't match records in another table based on > a common column in the two tables. Both tables have a column named > 'key100'. I was trying something like: > > SELECT count(*) > FROM table1, table2 > WHERE (table1.key100 != table2.key100); > > But the query is very slow and I finally just cancel it. Any help is > very much appreciated. Do you have indices on the key100 columns? Is autovacuum running, or do you do analyze manually? Can you send us the output from "EXPLAIN ANALYZE [your query]"? Btw, I don't think this query will do what you wanted, it basically creates a cross product, that means if your tables look like: schabitest=# select * from table1; key100 | valuea | valueb ++ 1 | foo| bar 2 | blah | blubb 3 | manga | mungo schabitest=# select * from table2; key100 | valuec | valued ++ 1 | monday | euro 2 | sunday | dollar 4 | friday | pounds Then your query will produce something like: schabitest=# select * from table1, table2 WHERE (table1.key100 != table2.key100); key100 | valuea | valueb | key100 | valuec | valued +++++ 1 | foo| bar| 2 | sunday | dollar 1 | foo| bar| 4 | friday | pounds 2 | blah | blubb | 1 | monday | euro 2 | blah | blubb | 4 | friday | pounds 3 | manga | mungo | 1 | monday | euro 3 | manga | mungo | 2 | sunday | dollar 3 | manga | mungo | 4 | friday | pounds I suggest you would like to have all records from table1 that don't have a corresponding record in table2: schabitest=# select * from table1 where table1.key100 not in (select key100 from table2); key100 | valuea | valueb ++ 3 | manga | mungo HTH, Markus Here is my query SQL: SELECT key100 FROM ncccr10 WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9); It is is running after 30 minutes. Here is the query plan: QUERY PLAN - Seq Scan on ncccr10 (cost=0.00..20417160510.08 rows=305782 width=104) Filter: (NOT (subplan)) SubPlan -> Seq Scan on ncccr9 (cost=0.00..65533.71 rows=494471 width=104) (4 rows) Any ideas why it is so slow?
Re: [SQL] Non Matching Records in Two Tables
Ken Hill <[EMAIL PROTECTED]> writes: > Seq Scan on ncccr10 (cost=0.00..20417160510.08 rows=305782 width=104) >Filter: (NOT (subplan)) >SubPlan > -> Seq Scan on ncccr9 (cost=0.00..65533.71 rows=494471 width=104) > (4 rows) > Any ideas why it is so slow? "NOT (subplan)" is horrendous (and the system knows it, note the huge cost estimate). Try increasing work_mem enough so you get a hashed subplan instead. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Non Matching Records in Two Tables
> Here is my query SQL: > > SELECT key100 FROM ncccr10 > WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9); > > It is is running after 30 minutes. Here is the query plan: > I would try an outer join: select a.key100 from ncccr10 a left join ncccr9 b on( key100 ) where b.key100 is null; also (hate to be obvious) have you analyzed lately? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] create table and data types
Hello pgsql-sql, Is anybody know how create field in a new table with data type accuiring from a field in other table? For example: create table new_table ( name other_table.name%TYPE ); -- Best regards, Maciej mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Non Matching Records in Two Tables
On Tue, 2006-02-14 at 13:08 -0800, chester c young wrote: > Here is my query SQL: > > SELECT key100 FROM ncccr10 > WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9); > > It is is running after 30 minutes. Here is the query plan: > I would try an outer join: select a.key100 from ncccr10 a left join ncccr9 b on( key100 ) where b.key100 is null; also (hate to be obvious) have you analyzed lately? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Yes, I did a VACUUM ANALYZE on the entire database before running this query. Also, I get an error with your suggestion: csalgorithm=# SELECT a.key100 FROM ncccr10 a csalgorithm-# LEFT JOIN ncccr9 b ON(a.key100) csalgorithm-# WHERE b.key100 IS Null; ERROR: argument of JOIN/ON must be type boolean, not type character
Re: [SQL] Non Matching Records in Two Tables
On Tue, 2006-02-14 at 16:07 -0500, Tom Lane wrote: Ken Hill <[EMAIL PROTECTED]> writes: > Seq Scan on ncccr10 (cost=0.00..20417160510.08 rows=305782 width=104) >Filter: (NOT (subplan)) >SubPlan > -> Seq Scan on ncccr9 (cost=0.00..65533.71 rows=494471 width=104) > (4 rows) > Any ideas why it is so slow? "NOT (subplan)" is horrendous (and the system knows it, note the huge cost estimate). Try increasing work_mem enough so you get a hashed subplan instead. regards, tom lane How do I ncrease work_mem?
Re: [SQL] create table and data types
On Tue, 2006-02-14 at 22:12 +0100, Maciej Piekielniak wrote: Hello pgsql-sql, Is anybody know how create field in a new table with data type accuiring from a field in other table? For example: create table new_table ( name other_table.name%TYPE ); Have you tried inheritance from one table to the new table? CREATE TABLE new_table (new_column) INHERITS (old_table) All columns in 'old_table' will be inclueded in 'new_table' plus the column 'new_column'.
Re: [SQL] create table and data types
Hello Ken, Tuesday, February 14, 2006, 10:30:34 PM, you wrote: KH> On Tue, 2006-02-14 at 22:12 +0100, Maciej Piekielniak wrote: >> Hello pgsql-sql, >> >> Is anybody know how create field in a new table with data type accuiring >> from a field in other table? >> For example: >> >> create table new_table >> ( >> name other_table.name%TYPE >> ); >> KH> Have you tried inheritance from one table to the new table? KH> CREATE TABLE new_table (new_column) KH> INHERITS (old_table) KH> All columns in 'old_table' will be inclueded in 'new_table' plus the KH> column 'new_column'. yes, but i don't need all colums, i need only the same data type for only some fields. for example create table new table ( name other_table.name%TYPE, mynewfield VARCHAR(100), mynewfield2 VARCHAR(100) ); -- Best regards, Maciej ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] create table and data types
Maciej Piekielniak <[EMAIL PROTECTED]> writes: > Is anybody know how create field in a new table with data type accuiring > from a field in other table? Sorry, the %TYPE syntax only works in function declarations at the moment. It could possibly be made to work in table declarations --- there are syntactic conflicts with allowing it in general, but I'm not sure that objection applies to table declarations. But it's not there today. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] create table and data types
On Tue, 2006-02-14 at 23:03 +0100, Maciej Piekielniak wrote:
Hello Ken,
Tuesday, February 14, 2006, 10:30:34 PM, you wrote:
KH> On Tue, 2006-02-14 at 22:12 +0100, Maciej Piekielniak wrote:
>> Hello pgsql-sql,
>>
>> Is anybody know how create field in a new table with data type accuiring from a field in other table?
>> For example:
>>
>> create table new_table
>> (
>> name other_table.name%TYPE
>> );
>>
KH> Have you tried inheritance from one table to the new table?
KH> CREATE TABLE new_table (new_column)
KH> INHERITS (old_table)
KH> All columns in 'old_table' will be inclueded in 'new_table' plus the
KH> column 'new_column'.
yes, but i don't need all colums, i need only the same data type for only
some fields.
for example
create table new table
(
name other_table.name%TYPE,
mynewfield VARCHAR(100),
mynewfield2 VARCHAR(100)
);
Have you tried restructuring the table with CREATE TABLE AS...? Try this:
CREATE TABLE new_table
(id, mynewfield, mynewfield2)
AS SELECT id FROM old_table);
This should create a new table ('new_table') with the data-type for 'old_table' for the id column.
Re: [SQL] create table and data types
On Tue, 2006-02-14 at 22:12 +0100, Maciej Piekielniak wrote: (snipped) >Is anybody know how create field in a new table with data type accuiring >from a field in other table? >For example: > create table new_table ( name other_table.name%TYPE); On Wed, 15 Feb 2006 09:42, Ken Hill wrote: (snipped) > Have you tried restructuring the table with CREATE TABLE AS...? > Try this: > CREATE TABLE new_table > (id, mynewfield, mynewfield2) > AS SELECT id FROM old_table); I use a similar technique, but add a "WHERE 1=0" so the new table is empty. Of course, more than a single table can be referenced in the FROM clause. -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Non Matching Records in Two Tables
On Tue, 2006-02-14 at 13:24 -0800, Ken Hill wrote: On Tue, 2006-02-14 at 13:08 -0800, chester c young wrote: > Here is my query SQL: > > SELECT key100 FROM ncccr10 > WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9); > > It is is running after 30 minutes. Here is the query plan: > I would try an outer join: select a.key100 from ncccr10 a left join ncccr9 b on( key100 ) where b.key100 is null; also (hate to be obvious) have you analyzed lately? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Yes, I did a VACUUM ANALYZE on the entire database before running this query. Also, I get an error with your suggestion: csalgorithm=# SELECT a.key100 FROM ncccr10 a csalgorithm-# LEFT JOIN ncccr9 b ON(a.key100) csalgorithm-# WHERE b.key100 IS Null; ERROR: argument of JOIN/ON must be type boolean, not type character Well, this works: SELECT * FROM ncccr9 a LEFT JOIN ncccr10 b USING( key100 ) WHERE b.key100 is null; It still seems slow. It takes about the same time to run as in MS Access. I thought PostgreSQL would be faster.
Re: [SQL] Non Matching Records in Two Tables
Ken Hill wrote: >> also (hate to be obvious) have you analyzed lately? >> I'd say that's fair game, not obvious. Vacuum/Analyze is ar so aparent to a person moving to Postgres from other DB's. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
