Re: [HACKERS] rewriter in updateable views
On Sat, 2005-03-19 at 01:10 -0500, Jaime Casanova wrote:
On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Jaime Casanova [EMAIL PROTECTED] writes:
... but if we do INSERT INTO vfoo(col2) values ('some_string) the
rewriter
cann resolv the value for col1. the reason is that views does not
inherit the defaults of the parent table. That is the reason you add
the ALTER TABLE ALTER COLUMN ADD/DROP DEFAULT for views.
Ok, this is a problem for us, so we want to improve the rewriter to
see the default in the base table an add it as appropiate.
Why do you not define the problem as when we decide a view is
updateable and create the needed rules for it, also create default
values for it by copying up from the base tables?
Well, that was our first thought. but what if the default value is
changed in the base table? then we have a problem, can we found in
what views we have to alter the default value in order to keep
consistency.
I can see that I might want the view to have a different default value
from that of the underlying table. I can see a reason to have multiple
updateable views on the same table, all with different columns, column
defaults and row selection clauses. (Multiple classes all held within
the same physical table, for example).
I'd suggest - if the default value for a column on a view IS NOT set,
then use the default value from the underlying table. If it IS set, then
it should stay set, even if the underlying table changes. That might
need some dependency logic in there...
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] rewriter in updateable views
Jaime Casanova [EMAIL PROTECTED] writes: On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane [EMAIL PROTECTED] wrote: Why do you not define the problem as when we decide a view is updateable and create the needed rules for it, also create default values for it by copying up from the base tables? Well, that was our first thought. but what if the default value is changed in the base table? So? Being able to have a different default for the view could be construed as a feature, not a bug. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] rewriter in updateable views
On Sat, 19 Mar 2005, Tom Lane wrote: Jaime Casanova [EMAIL PROTECTED] writes: On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane [EMAIL PROTECTED] wrote: Why do you not define the problem as when we decide a view is updateable and create the needed rules for it, also create default values for it by copying up from the base tables? Well, that was our first thought. but what if the default value is changed in the base table? So? Being able to have a different default for the view could be construed as a feature, not a bug. Except that if the view got its default by copying the base table default, (or not copying it if there isn't one) it should presumably mirror the base table's current default. However, if the view's default was explicitly set, it should probably ignore base table default changes. ---(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: [HACKERS] rewriter in updateable views
On Sat, 19 Mar 2005 11:42:18 +, Simon Riggs [EMAIL PROTECTED] wrote:
On Sat, 2005-03-19 at 01:10 -0500, Jaime Casanova wrote:
On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Jaime Casanova [EMAIL PROTECTED] writes:
... but if we do INSERT INTO vfoo(col2) values ('some_string) the
rewriter
cann resolv the value for col1. the reason is that views does not
inherit the defaults of the parent table. That is the reason you add
the ALTER TABLE ALTER COLUMN ADD/DROP DEFAULT for views.
Ok, this is a problem for us, so we want to improve the rewriter to
see the default in the base table an add it as appropiate.
Why do you not define the problem as when we decide a view is
updateable and create the needed rules for it, also create default
values for it by copying up from the base tables?
Well, that was our first thought. but what if the default value is
changed in the base table? then we have a problem, can we found in
what views we have to alter the default value in order to keep
consistency.
I can see that I might want the view to have a different default value
from that of the underlying table. I can see a reason to have multiple
updateable views on the same table, all with different columns, column
defaults and row selection clauses. (Multiple classes all held within
the same physical table, for example).
I'd suggest - if the default value for a column on a view IS NOT set,
then use the default value from the underlying table. If it IS set, then
it should stay set, even if the underlying table changes. That might
need some dependency logic in there...
And here is were we thought we have to improve the rewriter, if the
rewriter find a default value for a view it will use it if not it must
look for a default value in the base table.
regards,
Jaime Casanova
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] rewriter in updateable views
On Sat, 19 Mar 2005 11:05:39 -0500, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane [EMAIL PROTECTED] wrote: Why do you not define the problem as when we decide a view is updateable and create the needed rules for it, also create default values for it by copying up from the base tables? Well, that was our first thought. but what if the default value is changed in the base table? So? Being able to have a different default for the view could be construed as a feature, not a bug. regards, tom lane We are not against this. As you say this is a feature, but if the view doesn't have a default value we have to assign something in the appropiate col in the insert. ALTER TABLE view_name ALTER COLUMN ADD/DROP DEFAULT is your friend ;) regards, Jaime Casanova ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] GUC variable for setting number of local buffers
We've had a TODO item for some time about allowing the user to set the size of the local buffer array that's used for accessing temporary tables. The holdup has been that localbuf.c used very unscalable algorithms (like linear search) and so a large local buffer set would have terrible performance anyway. We wanted localbuf.c to duplicate the shared buffer manager's search and replacement algorithms, which looked like a lot of work. However, the recent changes to make the shared buffer manager use a clock sweep replacement algorithm made it trivial to have localbuf.c do the same. I have just committed additional changes to make localbuf.c use a hash table instead of linear search for lookup, so it's now fully on par with the shared buffer manager as far as algorithms go. That means we can go ahead with providing a GUC variable to make the array size user-selectable. I was thinking of calling it either local_buffers (in contrast to shared_buffers) or temp_buffers (to emphasize the fact that they're used for temporary tables). Anyone have a preference, or a better alternative? As far as semantics go, I was thinking of making the variable USERSET but allowing it to change only as long as you haven't accessed any temp tables in the current session. Under the hood, we'd postpone calling InitLocalBuffer() until the first use of temp tables in a session, at which time the local buffer descriptor array would be allocated, and henceforth you couldn't change the array size anymore. This would be enough flexibility to allow temp-table-intensive tasks to run with a large local setting, without having to make every session do the same. (It's conceivable that we could support on-the-fly resizing of the array, but it seems unlikely to be worth the trouble and risk of bugs.) It's already true that the individual buffers, as opposed to the buffer descriptors, are allocated only as needed; which makes the overhead of a large local_buffers setting pretty small if you don't actually do much with temp tables in a given session. So I was thinking about making the default value fairly robust, maybe 1000 (as compared to the historical value of 64...). Comments? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Very strange query difference between 7.3.6 and 7.4.6 (7.3.6 kicking 7.4.6 butt)
Specs: 7.3.6 machine Dual Athlon MP 2 GIG of ram, 4 Drive IDE (3ware) RAID 10 OS FC1 with 2.4 kernel 7.4.6 machine Dual Opteron MP (64bit PostgreSQL), 2 Gig of Ram 10 Drive RAID 10 with 128 Meg battery backed cache (3WARE). OS FC3 x86_64 with 2.6 kernel Essentials parameters: 7.3.6: shared_buffers = 8192 wal_buffers = 2048 sort_mem = 4096 checkpoint_segments = 25 effective_cache_size = 65536 random_page_cost = 1.5 statistics_target = 150 7.4.6 Same as above except 8192 sort mem and 50 checkpoint segments Both are running fsync with open_sync Both have been vacuumed and analyze repeatedly while trying to figure this out. Explain Analyzes: 7.3.6 (old) Aggregate (cost=320.49..324.89 rows=7 width=338) (actual time=630.21..630.21 rows=1 loops=1) - Group (cost=320.49..324.71 rows=70 width=338) (actual time=447.98..623.91 rows=8845 loops=1) - Sort (cost=320.49..320.67 rows=70 width=338) (actual time=447.95..460.77 rows=8845 loops=1) Sort Key: p.post_id, t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments - Nested Loop (cost=0.00..318.33 rows=70 width=338) (actual time=0.41..109.19 rows=8845 loops=1) - Nested Loop (cost=0.00..17.55 rows=1 width=330) (actual time=0.11..0.34 rows=1 loops=1) - Nested Loop (cost=0.00..6.75 rows=1 width=291) (actual time=0.08..0.12 rows=1 loops=1) - Index Scan using nuke_bbposts_pkey on nuke_bbposts p (cost=0.00..3.21 rows=1 width=8) (actual time=0.04..0.05 rows=1 loops=1) Index Cond: (post_id = 352888) - Index Scan using nuke_bbtopics_pkey on nuke_bbtopics t (cost=0.00..3.53 rows=1 width=283) (actual time=0.02..0.05 rows=1 loops=1) Index Cond: (t.topic_id = outer.topic_id) - Index Scan using nuke_bbforums_pkey on nuke_bbforums f (cost=0.00..10.78 rows=1 width=39) (actual time=0.02..0.21 rows=1 loops=1) Index Cond: (f.forum_id = outer.forum_id) - Index Scan using topic_id_nuke_bbposts_index on nuke_bbposts p2 (cost=0.00..299.33 rows=117 width=8) (actual time=0.29..31.68 rows=8845 loops=1) Index Cond: (p2.topic_id = outer.topic_id) Filter: (post_id = 352888) Total runtime: 633.72 msec (17 rows) 7.4.6 (new) GroupAggregate (cost=209.11..213.73 rows=71 width=328) (actual time=3701.837..3701.837 rows=1 loops=1) - Sort (cost=209.11..209.29 rows=71 width=328) (actual time=2725.518..2728.590 rows=8845 loops=1) Sort Key: p.post_id, t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments - Nested Loop (cost=0.00..206.93 rows=71 width=328) (actual time=0.621..190.112 rows=8845 loops=1) - Nested Loop (cost=0.00..9.04 rows=1 width=328) (actual time=0.347..0.365 rows=1 loops=1) - Nested Loop (cost=0.00..6.04 rows=1 width=291) (actual time=0.298..0.307 rows=1 loops=1) - Index Scan using nuke_bbposts_pkey on nuke_bbposts p (cost=0.00..3.01 rows=1 width=8) (actual time=0.209..0.212 rows=1 loops=1) Index Cond: (post_id = 352888) - Index Scan using nuke_bbtopics_pkey on nuke_bbtopics t (cost=0.00..3.01 rows=1 width=283) (actual time=0.069..0.073 rows=1 loops=1) Index Cond: (t.topic_id = outer.topic_id) - Index Scan using nuke_bbforums_pkey on nuke_bbforums f (cost=0.00..2.99 rows=1 width=39) (actual time=0.028..0.035 rows=1 loops=1) Index Cond: (f.forum_id = outer.forum_id) - Index Scan using topic_id_nuke_bbposts_index on nuke_bbposts p2 (cost=0.00..196.46 rows=114 width=8) (actual time=0.256..95.501 rows=8845 loops=1) Index Cond: (p2.topic_id = outer.topic_id) Filter: (post_id = 352888) Total runtime: 3728.376 ms (16 rows) If you look at the second line in each explain it is the sort that is causing the grief. On 7.3.6 it only takes say 447ms (on an completely unused machine), on the Opteron it takes 2725.518. The query on the opteron even after a fresh restart of apache and PostgreSQL takes at least 1100 ms. Other 7.4.6 information: [EMAIL PROTECTED]
Re: [HACKERS] Very strange query difference between 7.3.6 and 7.4.6 (7.3.6 kicking 7.4.6 butt)
Joshua D. Drake [EMAIL PROTECTED] writes: If you look at the second line in each explain it is the sort that is causing the grief. The aggregation is a great deal worse as well. I suspect that for some reason the comparison operations involved in the sorting and grouping are much slower on the FC3 machine. What are the data types of the leading sort keys ... and are you *certain* the FC3 database has LC_COLLATE and LC_CTYPE set to C? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Very strange query difference between 7.3.6 and 7.4.6
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: If you look at the second line in each explain it is the sort that is causing the grief. The aggregation is a great deal worse as well. I suspect that for some reason the comparison operations involved in the sorting and grouping are much slower on the FC3 machine. What are the data types of the leading sort keys ... and are you *certain* the FC3 database has LC_COLLATE and LC_CTYPE set to C? Well I definately did a initdb --no-locale 34 bin/initdb -D cdata --no-locale But I didn't specify LC_COLLATE or LC_CTYPE explicitly. I did set LANG=C in /etc/sysconfig/i18n however. Sincerely, Joshua D. Drake regards, tom lane -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(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: [HACKERS] Very strange query difference between 7.3.6 and 7.4.6
Well I definately did a initdb --no-locale 34 bin/initdb -D cdata --no-locale But I didn't specify LC_COLLATE or LC_CTYPE explicitly. I did set LANG=C in /etc/sysconfig/i18n however. Just to be specific... show all from psql: lc_collate | C lc_ctype | C lc_messages | C lc_monetary | C lc_numeric | C lc_time | C Sincerely, Joshua D. Drake Sincerely, Joshua D. Drake regards, tom lane ---(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 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(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: [HACKERS] Very strange query difference between 7.3.6 and 7.4.6 (7.3.6 kicking 7.4.6 butt)
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: The aggregation is a great deal worse as well. I suspect that for some reason the comparison operations involved in the sorting and grouping are much slower on the FC3 machine. What are the data types of the leading sort keys ... Data types are: post_id integer topic_id integer topic_title character(255) (I have no idea why ;)) forum_status and forum_id are smallints... The rest are pretty basic integers. Hm. What is the data like --- in particular, are the topic_ids unique in the data processed by the sort? I'm wondering how often the sort/group comparisons would even look at columns beyond the first two ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Very strange query difference between 7.3.6 and 7.4.6
The rest are pretty basic integers.
Hm. What is the data like --- in particular, are the topic_ids unique
in the data processed by the sort?
Yes topic_ids are the primary key. Here is the nuke_bbtopics structure:
Column| Type |
Modifiers
-++-
topic_id| integer| not null default
nextval('public.nuke_bbtopics_topic_id_seq'::text)
forum_id| smallint | not null default 0::smallint
topic_title | character(255) | not null default ''::bpchar
topic_poster| integer| not null default 0
topic_time | integer| not null default 0
topic_views | integer| not null default 0
topic_replies | integer| not null default 0
topic_status| smallint | not null default 0::smallint
topic_vote | smallint | not null default 0::smallint
topic_type | smallint | not null default 0::smallint
topic_last_post_id | integer| not null default 0
topic_first_post_id | integer| not null default 0
topic_moved_id | integer| not null default 0
news_id | integer| not null default 0
Indexes:
nuke_bbtopics_pkey primary key, btree (topic_id)
forum_id_nuke_bbtopics btree (forum_id)
nuke_bbtopics_news_id btree (news_id)
topic_last_post_id_nuke_bbtopics btree (topic_last_post_id)
topic_type_nuke_bbtopics btree (topic_type)
topic_vote_nuke_bbtopics btree (topic_vote)
Check constraints:
$6 CHECK (topic_moved_id = 0)
$5 CHECK (topic_first_post_id = 0)
$4 CHECK (topic_last_post_id = 0)
$3 CHECK (topic_replies = 0)
$2 CHECK (topic_views = 0)
$1 CHECK (forum_id = 0)
And the nuke_bbposts structure:
Table public.nuke_bbposts
Column | Type | Modifiers
-+---+---
post_id | integer | not null default
nextval('nuke_bbposts_id_seq'::text)
topic_id| integer | not null default 0
forum_id| smallint | not null default 0::smallint
poster_id | integer | not null default 0
post_time | integer | not null default 0
poster_ip | character varying(8) | not null default
''::character varying
post_username | character varying(25) |
enable_bbcode | smallint | not null default 1::smallint
enable_html | smallint | not null default 0::smallint
enable_smilies | smallint | not null default 1::smallint
enable_sig | smallint | not null default 1::smallint
post_edit_time | integer |
post_edit_count | smallint | not null default 0::smallint
Indexes:
nuke_bbposts_pkey primary key, btree (post_id)
forum_id_nuke_bbposts_index btree (forum_id)
post_time_nuke_bbposts_index btree (post_time)
poster_id_nuke_bbposts_index btree (poster_id)
topic_id_nuke_bbposts_index btree (topic_id)
Check constraints:
$3 CHECK (post_edit_count = 0)
$2 CHECK (forum_id = 0)
$1 CHECK (topic_id = 0)
And the nuke_bbforums:
Table public.nuke_bbforums
Column | Type |
Modifiers
++--
forum_id | smallint | not null default
nextval('nuke_bbforums_forum_id_seq'::text)
cat_id | integer| not null default 0
forum_name | character varying(150) |
forum_desc | text |
forum_status | smallint | not null default 0::smallint
forum_order| integer| not null default 1
forum_posts| integer| not null default 0
forum_topics | integer| not null default 0
forum_last_post_id | integer| not null default 0
prune_next | integer|
prune_enable | smallint | not null default 1::smallint
auth_view | smallint | not null default 0::smallint
auth_read | smallint | not null default 0::smallint
auth_post | smallint | not null default 0::smallint
auth_reply | smallint | not null default 0::smallint
auth_edit | smallint | not null default 0::smallint
auth_delete| smallint | not null default 0::smallint
auth_sticky| smallint | not null default 0::smallint
auth_announce | smallint | not null default 0::smallint
auth_vote | smallint | not null default
Re: [HACKERS] Very strange query difference between 7.3.6 and 7.4.6 (7.3.6 kicking 7.4.6 butt)
Joshua D. Drake [EMAIL PROTECTED] writes: Hm. What is the data like --- in particular, are the topic_ids unique in the data processed by the sort? Yes topic_ids are the primary key. Here is the nuke_bbtopics structure: Hmmm ... because p.post_id has only a single value allowed by the WHERE clause, and that in turn determines single t and f rows, the SORT step is actually seeing thousands of rows that have all the *same* sort key. Ditto for the grouping step. I can't offhand see any change between 7.3 and 7.4 that would make 7.4 much worse on this corner case. Maybe the problem is in the glibc qsort() routine? It would be good to try the case in 7.3 and 7.4 on identical platforms. I have 7.3 and 7.4 built here on FC3, so if you don't, you could send me the data off-list. I'd just need the info going into the sort, ie create table foo as select p.post_id, t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments FROM nuke_bbtopics t, nuke_bbforums f, nuke_bbposts p, nuke_bbposts p2 WHERE p.post_id = 352888 AND t.topic_id = p.topic_id AND p2.topic_id = p.topic_id AND p2.post_id = 352888 AND f.forum_id = t.forum_id; and send a pg_dump of foo. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] GUC variable for setting number of local buffers
On Sat, 19 Mar 2005, Tom Lane wrote: That means we can go ahead with providing a GUC variable to make the array size user-selectable. I was thinking of calling it either local_buffers (in contrast to shared_buffers) or temp_buffers (to emphasize the fact that they're used for temporary tables). Anyone have a preference, or a better alternative? temp_buffers sounds more descriptive ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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: [HACKERS] Very strange query difference between 7.3.6 and 7.4.6 (7.3.6 kicking 7.4.6 butt)
Joshua D. Drake [EMAIL PROTECTED] writes: Let me know what you come up with. Thanks for the help. Hmph. On my FC3 machine, 7.4 is consistently faster than 7.3 in sorting and grouping this data --- it's about 710 vs 960 msec. (This is on a P4 1.8GHz, presumably slower than your machines.) So there's no algorithmic change that might be biting us. It seems we have to look at the platforms involved. At this point I can think of two hypotheses that haven't been eliminated: 1. FC1's qsort is much faster than FC3's on this case. 2. The 64-bit build has got some kind of performance problem that's not generic to 7.4.*. #1 doesn't seem very probable, though it's possible. I think what you should do next is build 7.3 on the 64-bit machine and see what performance it's got. You might also try non-64-bit builds and see what they do. Just FYI, you can test the behavior without loading your full database --- just load the data you sent me and do explain analyze select count(*) from foo group by post_id, topic_id, topic_title, topic_status, topic_replies, topic_time, topic_type, topic_vote, topic_last_post_id, forum_name, forum_status, forum_id, auth_view, auth_read, auth_post, auth_reply, auth_edit, auth_delete, auth_sticky, auth_announce, auth_pollcreate, auth_vote, auth_attachments ; On 7.4 and up you may have to set enable_hashagg = off to force a Sort/GroupAggregate plan instead of HashAggregate. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Very strange query difference between 7.3.6 and 7.4.6
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Let me know what you come up with. Thanks for the help. Hmph. On my FC3 machine, 7.4 is consistently faster than 7.3 in sorting and grouping this data --- it's about 710 vs 960 msec. (This is on a P4 1.8GHz, presumably slower than your machines.) So there's no algorithmic change that might be biting us. It seems we have to look at the platforms involved. At this point I can think of two hypotheses that haven't been eliminated: 1. FC1's qsort is much faster than FC3's on this case. 2. The 64-bit build has got some kind of performance problem that's not generic to 7.4.*. #1 doesn't seem very probable, though it's possible. I think what you should do next is build 7.3 on the 64-bit machine and see what performance it's got. You might also try non-64-bit builds and see what they do. O.k. thanks for the help. I will take a look and let you know the results. Sincerely, Joshua D. Drake Just FYI, you can test the behavior without loading your full database --- just load the data you sent me and do explain analyze select count(*) from foo group by post_id, topic_id, topic_title, topic_status, topic_replies, topic_time, topic_type, topic_vote, topic_last_post_id, forum_name, forum_status, forum_id, auth_view, auth_read, auth_post, auth_reply, auth_edit, auth_delete, auth_sticky, auth_announce, auth_pollcreate, auth_vote, auth_attachments ; On 7.4 and up you may have to set enable_hashagg = off to force a Sort/GroupAggregate plan instead of HashAggregate. regards, tom lane -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] GUC variable for setting number of local buffers
Tom Lane wrote: That means we can go ahead with providing a GUC variable to make the array size user-selectable. I was thinking of calling it either local_buffers (in contrast to shared_buffers) or temp_buffers (to emphasize the fact that they're used for temporary tables). Anyone have a preference, or a better alternative? temp_buffers (or even temporary_buffers) makes it nice and clear what they are intended for. cheers Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Avoiding unnecessary writes during relation drop and truncate
Currently, in places like heap_drop_with_catalog, we issue a FlushRelationBuffers() call followed by smgrscheduleunlink(). The latter doesn't actually do anything right away, but schedules a file unlink to occur after transaction commit. It strikes me that the FlushRelationBuffers call is unnecessary and causes useless I/O, namely writing out pages into a file that's about to be deleted anyway. If we simply removed it then any buffers belonging to the victim relation would stay in memory until commit; then they'd be dropped *without* write by the smgr unlink operation (which already calls DropRelFileNodeBuffers). This doesn't cause any problems with rolling back the transaction before commit; we can perfectly well leave dirty pages in the buffer pool in that case. About the only downside I can see is that the Flush allows buffer pages to be freed slightly sooner, and hence possibly used for something else later in the same transaction ... but that's hardly worth the cost of writing data that might not need to be written at all. Similar remarks apply to the partial FlushRelationBuffers calls that are currently done just before partial or full truncation of a relation --- except that those are even sillier, because we are writing data that we are definitely going to tell the kernel to forget about immediately afterward. We should just drop any buffers that are past the truncation point. smgrtruncate isn't roll-back-able anyway, so the caller already has to be certain that the pages aren't going to be needed anymore regardless of any subsequent rollback. Can anyone see a flaw in this logic? I think that the FlushRelationBuffers calls associated with deletion are leftover from a time when we actually deleted the target file immediately (ie, back when DROP TABLE wasn't rollback-safe). The ones associated with truncation were probably just modeled on the deletion logic without sufficient thought. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests
Thanks to Andrew Dunstan, I found the cause of these link errors.
Andrew found this in libintl:
#undef snprintf
#define snprintf libintl_snprintf
extern int snprintf (char *, size_t, const char *, ...);
What is happening is that we do:
#define snprintfpg_snprintf
and then libintl.h (?) does:
#define snprintf libintl_snprintf
so the effect is:
#define pg_snprintf libintl_snprintf
In fact, in this example, the system complains about a missing X3 symbol:
#define X1 X2
#define X2 X3
int
main(int argc, char *argv[])
{
X1;
}
so the effet of the defines is:
#define X1 X3
Anyway, the reason ecpg is failing is that it is the only client-side
program that doesn't use libintl for internationalization. It is on our
TODO list to do that, but it hasn't been done yet.
However, only Win32 is seeing this failure, and only when configure
--enable-nls. I think this is because only Win32 does the redefine of
snprint and friends.
Comments?
---
Nicolai Tufar wrote:
On Wed, 16 Mar 2005 01:00:21 -0500 (EST), Bruce Momjian
pgman@candle.pha.pa.us wrote:
I have applied a modified version of your patch, attached.
I am so sorry, I sent untested patch again. Thank you very
much for patience in fixing it. The patch looks perfectly
fine and works under Solaris.
Under win32 I am still struggling with build environment.
In many directories link fails with undefined reference to
`pg_snprintf' in other it fails with undefined reference to
`_imp__libintl_sprintf'. In yet another directory it fails with
both, like in src/interfaces/ecpg/pgtypeslib:
dlltool --export-all --output-def pgtypes.def numeric.o datetime.o
common.o dt_common.o timestamp.o interval.o pgstrcasecmp.o
dllwrap -o libpgtypes.dll --dllname libpgtypes.dll --def pgtypes.def
numeric.o datetime.o common.o dt_common.o timestamp.o interval.o
pgstrcasecmp.o -L../../../../src/port -lm
numeric.o(.text+0x19ea):numeric.c: undefined reference to
`_imp__libintl_sprintf'
datetime.o(.text+0x476):datetime.c: undefined reference to `pg_snprintf'
common.o(.text+0x1cd):common.c: undefined reference to `pg_snprintf'
common.o(.text+0x251):common.c: undefined reference to `pg_snprintf'
dt_common.o(.text+0x538):dt_common.c: undefined reference to
`_imp__libintl_sprintf'
dt_common.o(.text+0x553):dt_common.c: undefined reference to
`_imp__libintl_sprintf'
dt_common.o(.text+0x597):dt_common.c: undefined reference to
`_imp__libintl_sprintf'
dt_common.o(.text+0x5d5):dt_common.c: undefined reference to
`_imp__libintl_sprintf'
dt_common.o(.text+0x628):dt_common.c: undefined reference to
`_imp__libintl_sprintf'
dt_common.o(.text+0x7e8):dt_common.c: more undefined references to
`_imp__libintl_sprintf' follow
c:\MinGW\bin\dllwrap.exe: c:\MinGW\bin\gcc exited with status 1
make: *** [libpgtypes.a] Error 1
Could someone with a better grasp of configure and
win32 environment check it? Aparently no one regularily
compiles source code under win32 during development cycle
these days.
Best regards,
Nicolai
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (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 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests
Bruce Momjian pgman@candle.pha.pa.us writes: so the effect is: #define pg_snprintf libintl_snprintf That's not how CPP works. In fact, in this example, the system complains about a missing X3 symbol: #define X1 X2 #define X2 X3 In this case any occurrence of X1 replaced by X2 but then the result is rescanned for macros and X2 is turned into X3. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
