[SQL] Postgres 7.4.9 slow!
Hello! I'm not sure if this belongs to this mailing list, if not, please tell me to redirect to where it belongs. I have a query that does COUNT, LEFT JOIN and GROUP BY from two tables. One has 85000 records, and other has 100 records. I've been running the tests on 7.4.3, with SET ENABLE_SEQSCAN TO OFF, and I get 9-11 seconds for that query. Query plan shows that postgres is using both indexes on both tables (one index is set on primary key, naturaly, and other is manualy set on foreign key in 'child' table). That is acceptable. But, now I downloaded postgres 7.4.9, and i'm running the very same query on the very same database with all the indices and constraints beeing the same (also SET ENABLE_SEQSCAN TO OFF), and I get around 90-110 seconds. Has anyone noticed extreeme slowdown of postgres 7.4.9? Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Postgres 7.4.9 slow!
Mike, Please send the EXPLAIN ANALYZE of the two versions of the query. Best Regards, Otto - Original Message - From: "Mario Splivalo" <[EMAIL PROTECTED]> To: Sent: Friday, October 21, 2005 1:13 PM Subject: [SQL] Postgres 7.4.9 slow! Hello! I'm not sure if this belongs to this mailing list, if not, please tell me to redirect to where it belongs. I have a query that does COUNT, LEFT JOIN and GROUP BY from two tables. One has 85000 records, and other has 100 records. I've been running the tests on 7.4.3, with SET ENABLE_SEQSCAN TO OFF, and I get 9-11 seconds for that query. Query plan shows that postgres is using both indexes on both tables (one index is set on primary key, naturaly, and other is manualy set on foreign key in 'child' table). That is acceptable. But, now I downloaded postgres 7.4.9, and i'm running the very same query on the very same database with all the indices and constraints beeing the same (also SET ENABLE_SEQSCAN TO OFF), and I get around 90-110 seconds. Has anyone noticed extreeme slowdown of postgres 7.4.9? Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Postgres 7.4.9 slow!
On Fri, 2005-10-21 at 14:01 +0200, Havasvölgyi Ottó wrote:
> Mike,
>
> Please send the EXPLAIN ANALYZE of the two versions of the query.
There they are, they are both the same:
join_test=# select version();
version
--
PostgreSQL 7.4.9 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.2
20050808 (prerelease) (Ubuntu 4.0.1-4ubuntu9)
(1 row)
join_test=# \timing
Timing is on.
join_test=# set enable_seqscan to off;
SET
Time: 0.715 ms
join_test=# explain select p.phone, count(*) from phones p left join
table_data d on p.phone = d.phone group by p.phone having count(*) > 1
order by count(*) desc;
QUERY PLAN
-
Sort (cost=4993545.35..4993754.61 rows=83704 width=16)
Sort Key: count(*)
-> GroupAggregate (cost=0.00..4985814.87 rows=83704 width=16)
Filter: (count(*) > 1)
-> Merge Left Join (cost=0.00..4974843.57 rows=1379136
width=16)
Merge Cond: (("outer".phone)::text =
("inner".phone)::text)
-> Index Scan using pk1 on phones p (cost=0.00..2876.37
rows=83704 width=16)
-> Index Scan using "fki_fkTableData" on table_data d
(cost=0.00..4954515.15 rows=1379135 width=16)
(8 rows)
Time: 169.781 ms
join_test=#
And now the 7.4.8:
join_test=# select version();
version
--
PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5
(Debian 1:3.3.5-8ubuntu2)
(1 row)
join_test=# \timing
Timing is on.
join_test=# set enable_seqscan to off;
SET
Time: 0.500 ms
join_test=# explain select p.phone, count(*) from phones p left join
table_data d on p.phone = d.phone group by p.phone having count(*) > 1
order by count(*) desc;
QUERY PLAN
-
Sort (cost=4993545.35..4993754.61 rows=83704 width=16)
Sort Key: count(*)
-> GroupAggregate (cost=0.00..4985814.87 rows=83704 width=16)
Filter: (count(*) > 1)
-> Merge Left Join (cost=0.00..4974843.57 rows=1379136
width=16)
Merge Cond: (("outer".phone)::text =
("inner".phone)::text)
-> Index Scan using pk1 on phones p (cost=0.00..2876.37
rows=83704 width=16)
-> Index Scan using "fki_fkTableData" on table_data d
(cost=0.00..4954515.15 rows=1379135 width=16)
(8 rows)
Time: 31.510 ms
join_test=#
The plans are same. It's just that when I run the query with pg7.4.8 it
takes 100% of the processor time while running. pg7.4.9 takes 2-10%
while running. Disk activity is much more intense with pg7.4.9
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Postgres 7.4.9 slow!
On Friday 21 October 2005 14:34, Mario Splivalo wrote: | On Fri, 2005-10-21 at 14:01 +0200, Havasvölgyi Ottó wrote: | > Mike, | > | > Please send the EXPLAIN ANALYZE of the two versions of the query. ^^^ | There they are, they are both the same: | | join_test=# select version(); | version | --- |--- PostgreSQL 7.4.9 on | i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.2 20050808 (prerelease) | (Ubuntu 4.0.1-4ubuntu9) | (1 row) | | join_test=# \timing | Timing is on. | join_test=# set enable_seqscan to off; | SET | Time: 0.715 ms | join_test=# explain select p.phone, count(*) from phones p left join | table_data d on p.phone = d.phone group by p.phone having count(*) > 1 | order by count(*) desc; this is not what Otto requested; please send the output of EXPLAIN ANALYZE select p.phone, count(*) from phones p left join table_data d on p.phone = d.phone group by p.phone having count(*) > 1 order by count(*) desc; for both versions. Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(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
[SQL] Delete rule chain stops unexpectedly
Hi,
I've got the following table (contents not really relevant):
CREATE TABLE _rating_params (
id SERIAL PRIMARY KEY,
letter CHAR(1) NOT NULL CHECK (letter = 'E' OR letter = 'F'),
superparam_id INTEGER REFERENCES _rating_params,
seq_num INTEGER NOT NULL DEFAULT 1,
name_id INTEGER NOT NULL REFERENCES messages_eng,
max_score NUMERIC(4)
);
which I manipulate with the view "rating_params". The delete rules on this view
act very strangely. They are, with comments I'll explain:
-- Actually delete the rating param, along with all it's subparams
CREATE RULE delete1 AS ON DELETE TO rating_params DO INSTEAD (
INSERT INTO debuglog (line) VALUES('step1');
-- When I comment out this line, then the other rules _are_ executed. If I
leave it here, execution stops here, after this query.
DELETE FROM _rating_params WHERE id = OLD.id OR superparam_id=OLD.id;
INSERT INTO debuglog (line) VALUES('step2');
);
-- Renumber sequences in order not to get any gaps
CREATE RULE delete2 AS ON DELETE TO rating_params DO ALSO (
UPDATE _rating_params SET seq_num = seq_num - 1
WHERE superparam_id = OLD.superparam_id AND seq_num > OLD.seq_num;
INSERT INTO debuglog (line) VALUES('step3');
);
-- Remove the max. score from any maximum total scores
CREATE RULE delete3 AS ON DELETE TO rating_params WHERE OLD.superparam_id IS NOT
NULL DO ALSO (
UPDATE _rating_params SET max_score = rating_param_max_score(id)
WHERE id = OLD.superparam_id;
INSERT INTO debuglog (line) VALUES('step4');
);
As you can see I've put several debug messages in the rules. As it is now, only
step1 is put in the debuglog table. When I remove the query to delete from the
_rating_params table, all other rules are executed and the debug messages are
inserted.
The strangest is yet to come. Normally I would delete with "delete from
rating_params where id=5". But, when I do "explain analyze delete from
rating_params where id=5", all the rules _are_ executed properly.
I'm using postgresql 8.0.3.
Anybody got an idea?
Thanks in advance.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Postgres 7.4.9 slow!
Mario Splivalo <[EMAIL PROTECTED]> writes: > I have a query that does COUNT, LEFT JOIN and GROUP BY from two tables. > One has 85000 records, and other has 100 records. I've been running > the tests on 7.4.3, Your later message shows 7.4.8. Which is it? > But, now I downloaded postgres 7.4.9, and i'm running the very same > query on the very same database with all the indices and constraints > beeing the same (also SET ENABLE_SEQSCAN TO OFF), and I get around > 90-110 seconds. You sure it's the very same? The version outputs suggest that these might be two different machines; certainly two very different compilers were used. One thing I'd wonder about is whether both databases were initialized in the same locale. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Postgres 7.4.9 slow!
On Fri, 2005-10-21 at 10:20 -0400, Tom Lane wrote: > Mario Splivalo <[EMAIL PROTECTED]> writes: > > I have a query that does COUNT, LEFT JOIN and GROUP BY from two tables. > > One has 85000 records, and other has 100 records. I've been running > > the tests on 7.4.3, > > Your later message shows 7.4.8. Which is it? > > > But, now I downloaded postgres 7.4.9, and i'm running the very same > > query on the very same database with all the indices and constraints > > beeing the same (also SET ENABLE_SEQSCAN TO OFF), and I get around > > 90-110 seconds. > > You sure it's the very same? The version outputs suggest that these > might be two different machines; certainly two very different compilers > were used. One thing I'd wonder about is whether both databases were > initialized in the same locale. Yes, I realized that the new Ubuntu distribution hac gcc4 by default. I'll compile again both 7.4.8 and 7.4.9 and my home PC, and see what happens then. My mention of 7.4.3 is a typo. It's 7.4.8 and 7.4.9. Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Delete rule chain stops unexpectedly
Wiebe Cazemier <[EMAIL PROTECTED]> writes: > which I manipulate with the view "rating_params". The delete rules on this > view > act very strangely. The rule that actually deletes the rows from the underlying has to fire last, since the rows are gone from the view (and hence from OLD) the moment you delete them. In practice, you'd be way better off using an ON DELETE trigger for these tasks. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Delete rule chain stops unexpectedly
Tom Lane wrote: > The rule that actually deletes the rows from the underlying has to fire > last, since the rows are gone from the view (and hence from OLD) the > moment you delete them. A quote from the postgresql manual: "But for ON UPDATE and ON DELETE rules, the original query is done after the actions added by rules. This ensures that the actions can see the to-be-updated or to-be-deleted rows" So, the actual delete should be done after all the rules. And even if it does delete before anything else, that does not explain why "step2" is not inserted into the debuglog table. Or, that all the rules _are_ executed when I call the query with "explain analayze". ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Delete rule chain stops unexpectedly
Wiebe Cazemier <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >>> The rule that actually deletes the rows from the underlying has to fire >>> last, since the rows are gone from the view (and hence from OLD) the >>> moment you delete them. > A quote from the postgresql manual: > "But for ON UPDATE and ON DELETE rules, the original query is done after the > actions added by rules. This ensures that the actions can see the > to-be-updated > or to-be-deleted rows" Yes, I know that quote. I wrote it. It's not relevant here because the original query (the delete against the view) is never done at all, since you have a DO INSTEAD rule. What is relevant is the timing of the delete issued against the underlying table, and you have that scheduled to occur too early because the rule that does it is first in alphabetical order. > So, the actual delete should be done after all the rules. And even if > it does delete before anything else, that does not explain why "step2" > is not inserted into the debuglog table. Because the rule converts those inserts into, effectively, INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row); and there are no longer any matching OLD rows in the view. (If it didn't act that way then the INSERTs would execute even for a "DELETE WHERE false". If you find any of this surprising or not what you want, you should probably be using triggers not rules.) > Or, that all the rules _are_ > executed when I call the query with "explain analayze". Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should bump the CommandCounter between plan trees, but fails to ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Reading bytea field
Hi All,
sorry for posting another question on bytea fields, i searched in the
Forum and all over the net and could not find the answer, although it
seems pretty basic to me.
What i want to to is the follwing:
1. Create a picure in the Linux Shell
2. Insert into a bytea field
3. Retrieve the picture with OpenOffice.org Basic
4. Insert it into a Document.
Step 1 and 4. are not really postgresql relevant.
To solve step 2 i wrote a small c++ programm which inserts the jpeg
pictue into the bytea field. The relevant part looks like this:
struct stat sbuf;
stat(file.c_str(), &sbuf);
char fbuf[sbuf.st_size];
int fd = open(file.c_str(), O_RDONLY);
if (sbuf.st_size!=read(fd, fbuf, sizeof(fbuf)))
{
std::cerr << "Error reading: " << argv[1] << std::endl;
}
close(fd);
//unlink(file.c_str());
Oid in_oid[]={17, 23}; //{1043,17}; /* varchar, bytea */
const char* params[]={fbuf, nummern.at(i).c_str()};//{pkey,buf};
const int params_length[]={sizeof(fbuf),
nummern.at(i).size()};//{strlen(pkey),size};
const int params_format[]={1,0};//{0,1}; /* text,binary */
res = PQexecParams(conn,
"UPDATE Artikel SET barcode=$1 WHERE nummer=$2",
2,/* one param */
in_oid, params, params_length,
params_format, 1);
if (res && PQresultStatus(res)==PGRES_COMMAND_OK) {
std::cout << "Glück" << std::endl;
}
else
{
std::cout << PQresultErrorMessage(res) << std::endl;
}
I read that it is better to use PQexecParams instead of PQescape. In the
bytea field is then the picture with every Byte escaped. So the first
few bytes look like this:
\377\330\377\340\000\020JFIF ...
I wouldn't bother if i knew how to unescape this sequence. I supposed
that this is more or less an internal representation, but when i try to
retrieve the data. i get it like this.
If, on the other hand, i encode the bytea to a text field, the result
looks nearly good. Just the \000 character stays the same, and maybe the
three others that have to be escaped.
Isn't there a way to just extract from the bytea field that what i put
in there?
Thanks for any help in advance,
Jochen
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Delete rule chain stops unexpectedly
Tom Lane wrote: A quote from the postgresql manual: "But for ON UPDATE and ON DELETE rules, the original query is done after the actions added by rules. This ensures that the actions can see the to-be-updated or to-be-deleted rows" Yes, I know that quote. I wrote it. It's not relevant here because the original query (the delete against the view) is never done at all, since you have a DO INSTEAD rule. What is relevant is the timing of the delete issued against the underlying table, and you have that scheduled to occur too early because the rule that does it is first in alphabetical order. Oh yeah, whoops, I was under a false impression of what the original query was... Because the rule converts those inserts into, effectively, INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row); and there are no longer any matching OLD rows in the view. (If it didn't act that way then the INSERTs would execute even for a "DELETE WHERE false". If you find any of this surprising or not what you want, you should probably be using triggers not rules.) Ah, I see. I'll convert them to triggers, or just put the delete at the end. I could even put all this in one rule, that would be easier. Or, that all the rules _are_ executed when I call the query with "explain analayze". Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should bump the CommandCounter between plan trees, but fails to ... Is this something I have to report? ---(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
Re: [SQL] Delete rule chain stops unexpectedly
Wiebe Cazemier <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should >> bump the CommandCounter between plan trees, but fails to ... > Is this something I have to report? Nah, I fixed it already (only in CVS HEAD though). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Blank-padding (was: Oracle buys Innobase)
On 2005-10-21 09:47, Tom Lane wrote:
Alex Turner <[EMAIL PROTECTED]> writes:
It appears that casting to a char() causes spaces to be stripped (ignored) from
the string:
mls=# select length('123 '::char(8));
length
3
(1 row)
I'm not sure about anyone else, but I would personaly consider that a bug?
No, it's a feature, as per extensive discussion some time ago when we made it
do that. The general rule is that trailing spaces in a char(n) are
semantically insignificant.
regards, tom lane
I remember that discussion, and I was for the change. However, upon
doing some testing after reading the above, I wonder if the
blank-stripping isn't too aggressive. I have a CHAR(6) field (say,
named Z) that has "abc " in it. Suppose I want to append "x" to Z,
with any leading spaces in Z PRESERVED. The following do not work in 8.0.4:
select Z || 'x';
select Z::char(6) || 'x';
select Z::varchar(6) || 'x';
select (Z || ' ')::char(6) || 'x';
There are only two ways I've found:
select rpad( Z, 6) || 'x'; -- but "rpad" is apparently not a
SQL-standard function.
select cast (Z || ' ' as varchar(6)) || 'x'; -- hokey but
SQL-compliant
Is there something I'm missing???
-- Dean
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Blank-padding (was: Oracle buys Innobase)
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: > I remember that discussion, and I was for the change. However, upon > doing some testing after reading the above, I wonder if the > blank-stripping isn't too aggressive. I have a CHAR(6) field (say, > named Z) that has "abc " in it. Suppose I want to append "x" to Z, > with any leading spaces in Z PRESERVED. (You meant trailing spaces, I assume.) Why exactly would you want to do that? You decided by your choice of datatype that the trailing spaces weren't significant. This gripe seems to me exactly comparable to complaining if a numeric datatype doesn't remember how many trailing zeroes you typed after the decimal point. Those zeroes aren't semantically significant, so you have no case. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Blank-padding
Tom Lane wrote: "Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: I remember that discussion, and I was for the change. However, upon doing some testing after reading the above, I wonder if the blank-stripping isn't too aggressive. I have a CHAR(6) field (say, named Z) that has "abc " in it. Suppose I want to append "x" to Z, with any leading spaces in Z PRESERVED. (You meant trailing spaces, I assume.) Why exactly would you want to do that? You decided by your choice of datatype that the trailing spaces weren't significant. I once built a telecom billing app where this might be important (fixed length fields). Lets say you have fixed length fields defined as char(n) datatypes. You may want to build a query to generate billing records like: select field1 || field2 || field3 || field4 || field5 ... AS bill_record FROM lec_billing_entries; It seels to me that I would expect trailing spaces to be preserved in these cases. Having an implicit rtrim function is asking for problems. Personally I would rather have to call rtrim explicitly than have the backend treat the concatenation differently than if I do it on the client. This gripe seems to me exactly comparable to complaining if a numeric datatype doesn't remember how many trailing zeroes you typed after the decimal point. Those zeroes aren't semantically significant, so you have no case.\ My only gripe here is that the implicit rtrimming is going to cause problems in cases where you are trying to do things with fixed-length fields, which is really where one is likely to use bpchar anyway. It is not a showstopper, but I can see why some people don't like it. But can't please everyone :-) Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
