Re: [GENERAL] Advice on implementing counters in postgreSQL
On Sat, Aug 2, 2008 at 5:11 PM, David Fetter <[EMAIL PROTECTED]> wrote: > On Sat, Aug 02, 2008 at 09:23:31AM +0200, Marco Bizzarri wrote: >> Hi all. >> >> I need to keep a numer of counters in my application; my counters >> are currently stored in a table: >> >> name | next_value | year >> >> The counters must be progressive numbers with no holes in between >> them, and they must restart from 1 every year. > > Here's a backward-compatible way to do this: > > http://www.varlena.com/GeneralBits/130.php > > Cheers, > David. > -- > David Fetter <[EMAIL PROTECTED]> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: [EMAIL PROTECTED] > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > David, thanks for pointing me to such a complete solution. These work both on READ-COMMITTED and SERIALIZABLE isolation levels, am I right? Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice on implementing counters in postgreSQL
On Sun, Aug 3, 2008 at 1:40 AM, Berend Tober <[EMAIL PROTECTED]> wrote: > > > The way I understand the documentation at > > "http://www.postgresql.org/docs/8.3/static/transaction-iso.html"; > > and > > 'http://www.postgresql.org/docs/current/static/explicit-locking.html', > > you should not have to use the serial isolation level. > > I would define the counter table so as to hold the last-used value, rather > that the "next" value, and then do the UPDATE first. > > As a consequence, assuming all this happens within a transaction of course, > the SELECT FOR UPDATE syntax is not required either because the UPDATE will > grab a lock on the row and block other updates until the transaction is > finished. That is, concurrency is protected and you don't have to restart > any transactions because subsequent transactions will just wait until the > first one finishes due to nature of the lock automatically acquired by the > initial UPDATE statement. > Yes, I'm considering moving away from serializable; the problem is that I have to explore all the implications of this on my code. Up to now, I wrote considering a serializable level, so I think I should do quite a review to be sure about it. Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] missing chunk number 0 for toast value when pg_dump??
hi, all: I use pg_dump command to dump a specified table: pg_dump mydb -p -Uyahoo -t mytable -D -a > /export/dump.sql but, I got this: --- pg_dump: SQL command failed pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 14319826 pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor --- the table have about 3,000,000 records, but from the dump file, I got only 300,000 records with "INSERT INTO". my postgresql version is :8.3.3. thanks a lot. regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] bytea encode performance issues
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I am using postgresql 8.2.7 on gentoo for my dbmail backend.
I am also testing it on mysql 5.
I am trying to figure out if I need to tune my database configuration or
if querying a bytea field is just not practical in postgresql.
Searching with the mysql database takes under a minute and with the
postgresql database it takes approximately 10. It gets better when I fix
up the query a little, such as removing the group by and having and
including the clause as part of the where, but not anywhere close to the
mysql level.
This is the query that is used (I know it is not as efficient as it
could be, but this is the query it comes with):
SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_messages m
ON p.id = m.physmessage_id WHERE mailbox_idnr = 8 AND status IN (0,1 )
AND k.is_header = '0' GROUP BY m.message_idnr,k.messageblk HAVING
ENCODE(k.messageblk::bytea,'escape') LIKE '%John%'
The messageblk field is a bytea in postgresql and a longblob in mysql.
The only difference in the query is the MySQL does not need the encode
function.
I have plugged the query into the psql and mysql command line
applications, so I could evaluate the query without the application.
The database is using autovacuum and the estimated rows and the actual
rows are almost the same, so I assume it is working. There are 310266
rows in the dbmail_messageblks table.
Can someone make a suggestion for tuning the database?
The explain of the query is:
"HashAggregate (cost=43648.11..43648.85 rows=74 width=753)"
" -> Nested Loop (cost=42999.83..43647.74 rows=74 width=753)"
"-> Merge Join (cost=42999.83..43043.35 rows=74 width=769)"
" Merge Cond: (k.physmessage_id = m.physmessage_id)"
" -> Sort (cost=39264.12..39267.59 rows=1388 width=753)"
"Sort Key: k.physmessage_id"
"-> Seq Scan on dbmail_messageblks k
(cost=0.00..39191.68 rows=1388 width=753)"
" Filter: ((is_header = 0::smallint) AND
(encode(messageblk, 'escape'::text) ~~ '%John%'::text))"
" -> Sort (cost=3735.71..3754.59 rows=7552 width=16)"
"Sort Key: m.physmessage_id"
"-> Bitmap Heap Scan on dbmail_messages m
(cost=385.98..3249.26 rows=7552 width=16)"
" Recheck Cond: ((mailbox_idnr = 8) AND (status
= ANY ('{0,1}'::integer[])))"
" -> Bitmap Index Scan on dbmail_messages_8
(cost=0.00..384.10 rows=7552 width=0)"
"Index Cond: ((mailbox_idnr = 8) AND
(status = ANY ('{0,1}'::integer[])))"
"-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage p (cost=0.00..8.15 rows=1 width=8)"
" Index Cond: (k.physmessage_id = p.id)"
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkiVhHwACgkQjDX6szCBa+o6wACgwa05ZbUBL4Ef18N4JJHQ2SP1
gfwAnjIA14QktV/Qs1TrPiY+Ma+rmJht
=WOQM
-END PGP SIGNATURE-
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bytea encode performance issues
On Aug 3, 2008, at 12:12 PM, Sim Zacks wrote:
This is the query that is used (I know it is not as efficient as it
could be, but this is the query it comes with):
SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_messages m
ON p.id = m.physmessage_id WHERE mailbox_idnr = 8 AND status IN (0,1 )
AND k.is_header = '0' GROUP BY m.message_idnr,k.messageblk HAVING
ENCODE(k.messageblk::bytea,'escape') LIKE '%John%'
That LIKE operator is probably your problem. An unbounded LIKE like
that (with a wildcard on both sides) means no index can be used,
hence you get a sequential scan.
There are apparently some possibilities with the new GIN indexes (or
maybe even using GIST), but I haven't had an opportunity to try those
yet. There were some messages about just that on this list recently.
If you create an index, make sure you create a _functional_ index
over ENCODE(messageblk, 'escape').
The messageblk field is a bytea in postgresql and a longblob in
mysql.
The only difference in the query is the MySQL does not need the encode
function.
Since when is e-mail binary data? I don't quite see why you'd use a
bytea field instead of text. If your problem is character encoding,
then just don't store that ("encode" the DB using SQLASCII).
Can someone make a suggestion for tuning the database?
An explain analyze would have been a bit more useful, but the biggest
issue is probably the seqscan.
The explain of the query is:
"HashAggregate (cost=43648.11..43648.85 rows=74 width=753)"
" -> Nested Loop (cost=42999.83..43647.74 rows=74 width=753)"
"-> Merge Join (cost=42999.83..43043.35 rows=74 width=769)"
" Merge Cond: (k.physmessage_id = m.physmessage_id)"
" -> Sort (cost=39264.12..39267.59 rows=1388
width=753)"
"Sort Key: k.physmessage_id"
"-> Seq Scan on dbmail_messageblks k
(cost=0.00..39191.68 rows=1388 width=753)"
" Filter: ((is_header = 0::smallint) AND
(encode(messageblk, 'escape'::text) ~~ '%John%'::text))"
Here is your problem, a sequential scan over a presumably large
table. It's either caused by the LIKE expression or by the lack of a
functional index on messageblk, or both.
If you change the type of the messageblk field to text you won't need
a functional index anymore (although that only saves time on index
creation and inserts/updates).
" -> Sort (cost=3735.71..3754.59 rows=7552 width=16)"
"Sort Key: m.physmessage_id"
"-> Bitmap Heap Scan on dbmail_messages m
(cost=385.98..3249.26 rows=7552 width=16)"
" Recheck Cond: ((mailbox_idnr = 8) AND
(status
= ANY ('{0,1}'::integer[])))"
" -> Bitmap Index Scan on dbmail_messages_8
(cost=0.00..384.10 rows=7552 width=0)"
"Index Cond: ((mailbox_idnr = 8) AND
(status = ANY ('{0,1}'::integer[])))"
"-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage p (cost=0.00..8.15 rows=1 width=8)"
" Index Cond: (k.physmessage_id = p.id)"
I notice some other oddities in that query/design. Why is is_header a
smallint instead of a boolean? I'm assuming this is for compatibility
with an other database?
Why use status IN (0, 1) instead of more descriptive keys? Is it even
constrained this way, or could arbitrary numbers end up as status
(say 99) and if so, what happens to those messages?
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,48958f34243483105918576!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bytea encode performance issues
On Aug 3, 2008, at 2:36 PM, Sim Zacks wrote:
The LIKE operator is likely the problem, but it is a critical part
of an
email application. Searches are done by, "Show me all emails
containing
the following word."
I've tried using TSearch2's full text index. It made the query 50%
faster, taking 5 minutes. This is still not even close to the less
then
a minute in MySQL.
That LIKE operator is probably your problem. An unbounded LIKE
like that
(with a wildcard on both sides) means no index can be used, hence you
get a sequential scan.
There are apparently some possibilities with the new GIN indexes (or
maybe even using GIST), but I haven't had an opportunity to try those
yet. There were some messages about just that on this list recently.
I don't think a functional index (or anything other then a FTI) would
accomplish anything, being that I am doing unbounded Likes.
That's why I suggested to use a text field instead of bytea.
IIRC, You can have an index on word triplets and use tsearch. I don't
have intimate knowledge on how that works though, hopefully other
people will chime in here.
Without the need to convert each row before comparing it, and with an
appropriate index, that should significantly speed up your queries.
If you create an index, make sure you create a _functional_ index
over
ENCODE(messageblk, 'escape').
Email is binary when it contains attachments. I actually planned on
using an ASCII encoding, but the dbmail people specifically said
not to.
I don't know if they were speaking from experience, or because ASCII
sounds bad.
It shouldn't be; those attachments are MIME or UU encoded, are they not?
Don't confuse ASCII and SQLASCII. The latter accepts characters from
any encoding, which is probably what you want.
Since when is e-mail binary data? I don't quite see why you'd use a
bytea field instead of text. If your problem is character
encoding, then
just don't store that ("encode" the DB using SQLASCII).
As I mentioned, this is the system that came with dbmail. It runs on
both PostGresql and MySQL, so they may have done some compatibility
things. There are 4 statuses possible, 0,1,2,3 if you use the database
through the software then a 99 could never appear there.
The software isn't the only client that might connect to the database.
It is usually bad practice to put data constraint logic in the client
instead of in the database. Especially since in client code there are
usually multiple sections of code that have to deal with those
constraints, which tends to result in small differences in their
handling.
Next to that, if those statuses would have a proper foreign key
constraint, it would be very easy to add labels to each status in a
way they would make a bit more sense than 0, 1, 2, 3.
I expect the label would be a sufficient foreign key by itself
though, no need for those silly numbers.
Well, there's probably not much you can do about that, being just a
user of dbMail.
I notice some other oddities in that query/design. Why is is_header a
smallint instead of a boolean? I'm assuming this is for compatibility
with an other database?
Why use status IN (0, 1) instead of more descriptive keys? Is it even
constrained this way, or could arbitrary numbers end up as status
(say
99) and if so, what happens to those messages?
You have a very odd way of quoting. You don't top post as such, but
you top post in context... Haven't seen that one before. Usually
people reply _below_ a section, not above it ;) It made my reply a
bit harder to write.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4895b34b243488085013917!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bytea encode performance issues
The LIKE operator is likely the problem, but it is a critical part of an
email application. Searches are done by, "Show me all emails containing
the following word."
I've tried using TSearch2's full text index. It made the query 50%
faster, taking 5 minutes. This is still not even close to the less then
a minute in MySQL.
>
> That LIKE operator is probably your problem. An unbounded LIKE like that
> (with a wildcard on both sides) means no index can be used, hence you
> get a sequential scan.
>
> There are apparently some possibilities with the new GIN indexes (or
> maybe even using GIST), but I haven't had an opportunity to try those
> yet. There were some messages about just that on this list recently.
>
I don't think a functional index (or anything other then a FTI) would
accomplish anything, being that I am doing unbounded Likes.
> If you create an index, make sure you create a _functional_ index over
> ENCODE(messageblk, 'escape').
>
Email is binary when it contains attachments. I actually planned on
using an ASCII encoding, but the dbmail people specifically said not to.
I don't know if they were speaking from experience, or because ASCII
sounds bad.
> Since when is e-mail binary data? I don't quite see why you'd use a
> bytea field instead of text. If your problem is character encoding, then
> just don't store that ("encode" the DB using SQLASCII).
>
As I mentioned, this is the system that came with dbmail. It runs on
both PostGresql and MySQL, so they may have done some compatibility
things. There are 4 statuses possible, 0,1,2,3 if you use the database
through the software then a 99 could never appear there.
>
>
> I notice some other oddities in that query/design. Why is is_header a
> smallint instead of a boolean? I'm assuming this is for compatibility
> with an other database?
>
> Why use status IN (0, 1) instead of more descriptive keys? Is it even
> constrained this way, or could arbitrary numbers end up as status (say
> 99) and if so, what happens to those messages?
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:824,48958f30243481673380013!
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bytea encode performance issues
Sim Zacks <[EMAIL PROTECTED]> writes: > The explain of the query is: Could we see EXPLAIN ANALYZE, not EXPLAIN? Without actual facts to work from, any suggestions would be mere guesswork. Also, what can you tell us about the sizes of the messageblk strings (max and avg would be interesting)? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice on implementing counters in postgreSQL
On Sun, Aug 3, 2008 at 1:50 AM, Marco Bizzarri <[EMAIL PROTECTED]> wrote: > On Sun, Aug 3, 2008 at 1:40 AM, Berend Tober <[EMAIL PROTECTED]> wrote: >> >> >> The way I understand the documentation at >> >> "http://www.postgresql.org/docs/8.3/static/transaction-iso.html"; >> >> and >> >> 'http://www.postgresql.org/docs/current/static/explicit-locking.html', >> >> you should not have to use the serial isolation level. >> >> I would define the counter table so as to hold the last-used value, rather >> that the "next" value, and then do the UPDATE first. >> >> As a consequence, assuming all this happens within a transaction of course, >> the SELECT FOR UPDATE syntax is not required either because the UPDATE will >> grab a lock on the row and block other updates until the transaction is >> finished. That is, concurrency is protected and you don't have to restart >> any transactions because subsequent transactions will just wait until the >> first one finishes due to nature of the lock automatically acquired by the >> initial UPDATE statement. >> > > Yes, I'm considering moving away from serializable; the problem is > that I have to explore all the implications of this on my code. Up to > now, I wrote considering a serializable level, so I think I should do > quite a review to be sure about it. A fairly simple test shows that you can do this in read committed: S1: # show transaction_isolation; read committed (setup a table for the value) # create table t (i int); # insert into t values (5); S1: # begin; S1: # update t set i=i+1; S2: # update t set i=i+1; (S2 now waits for S1) S1: # select i from t; 6 S1: # commit; (S2 now can continue...) S2: # select i from t; 7 S2: # commit; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [EMAIL PROTECTED]
Hi all, I installed postgresql-8.3.3-1 for win2000 with UUID-OSSP. Following the documentation I issued this query: SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.postgresql.org'); but the DB returns with this error message: ERROR: function uuid_ns_url() does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 25 Any hint to use UUID within my database tirggers? Thank you, Laci -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
UUID in windows was Re: [GENERAL] [EMAIL PROTECTED]
Pretty sure the uuid stuff only works in non-windows OSes. Oh, and next time, use a more descriptive subject line for better / quicker responses. On Sun, Aug 3, 2008 at 2:18 PM, <[EMAIL PROTECTED]> wrote: > Hi all, > > I installed postgresql-8.3.3-1 for win2000 with UUID-OSSP. Following the > documentation I issued this query: > > SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.postgresql.org'); > > but the DB returns with this error message: > > ERROR: function uuid_ns_url() does not exist > SQL state: 42883 > Hint: No function matches the given name and argument types. You might need > to add explicit type casts. > Character: 25 > > Any hint to use UUID within my database tirggers? > > Thank you, > Laci > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] uuid
On Aug 3, 2008, at 1:18 PM, [EMAIL PROTECTED] wrote: ERROR: function uuid_ns_url() does not exist Remember to install the functions in your database using the SQL file in the contrib/uuid-ossp directory, uuid-ossp.sql. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [EMAIL PROTECTED]
Hi. Sorry, it was not included in release. please see, http://winpg.jp/~saito/pg_work/OSSP_win32/ Regards, Hiroshi Saito >Hi all, > >I installed postgresql-8.3.3-1 for win2000 with UUID-OSSP. Following >the documentation I issued this query: > >SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.postgresql.org'); > >but the DB returns with this error message: > >ERROR: function uuid_ns_url() does not exist >SQL state: 42883 >Hint: No function matches the given name and argument types. You might >need to add explicit type casts. >Character: 25 > >Any hint to use UUID within my database tirggers? > >Thank you, >Laci > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] recovery via base + WAL replay failure
I'm trying to demonstrate recovery using the "continuous archiving"
backup technique. I'm using 8.3 on Windows.
I made a base backup while the postgres was running using the following
batch file:
--
psql -d test_database -U user_name -c "SELECT pg_start_backup('test');"
7za a -tzip "C:\backup\base.zip" "C:\Program Files\PostgreSQL\8.3\data"
psql -d test_database -U user_name -c "SELECT pg_stop_backup();"
--
No indication of any errors. However, I am unable to recover. Here are
my steps:
1. Stop service, replace the data directory w/ the base backup.
2. Create recovery.conf with this setting:
restore_command = 'copy C:\backup\%f "%p"'
3. Try to start service
After about 90 seconds, the dos prompt displays:
"(postgres 8.3) service could not be started"
"The service did not report an error"
Does anyone know what I am probably doing wrong?
Thanks,
--Rob Adams
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [OT] newsreader issue? (Was: bytea encode performance issues)
Sim Zacks wrote: (quoting someone:) That LIKE operator is probably your problem. An unbounded LIKE like that (with a wildcard on both sides) means no index can be used, hence you get a sequential scan. Was the message to which you responded posted to the newsgroup? It isn't appearing in my newsreader. Who wrote the message you quoted (you failed to cite the source)? -- Lew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [OT] newsreader issue? (Was: bytea encode performance issues)
The [EMAIL PROTECTED] email address is invalid. I tried to send it this email: If I remember correctly, the news feed is gatewayed off the mailing list, so it's possible for a message to the list to not appear in the group if it gets dropped at the gateway. Sorry if this is redundant info for you. If you don't wish to receive spam on your regular email address (understandable) then either switch to reading and posting with a gmail account (got lots of invites, just ask) or set up a separate account to receive emails from pgsql and set up spam assassin and (/ or) a couple of whitelists and use that. It's kinda rude to ask me a question on a mailing list with an email address I can't respond to. I spend time writing up an answer that only you needed to see, and then can't send it to YOU, but only the whole list. On Sun, Aug 3, 2008 at 8:03 AM, Lew <[EMAIL PROTECTED]> wrote: > Sim Zacks wrote: >> >> (quoting someone:) >>> >>> That LIKE operator is probably your problem. An unbounded LIKE like that >>> (with a wildcard on both sides) means no index can be used, hence you >>> get a sequential scan. > > Was the message to which you responded posted to the newsgroup? It isn't > appearing in my newsreader. > > Who wrote the message you quoted (you failed to cite the source)? > > -- > Lew > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [OT] newsreader issue? (Was: bytea encode performance issues)
On 04/08/2008, Lew <[EMAIL PROTECTED]> wrote: > Was the message to which you responded posted to the newsgroup? It isn't > appearing in my newsreader. > > Who wrote the message you quoted (you failed to cite the source)? He was quoting Alban Hertroys, and it appeared on the general mailing list (I didn't even know there was a Newsgroup). There seems to be a problem with your mail address, however ... ;} -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [OT] newsreader issue? (Was: bytea encode performance issues)
One last thing. I'd rather my emails just get dropped silently if that's the minimum someone can do. Use a valid email address that goes to /dev/null and I'll be happy. You may miss a few things sent directly to you, but since that's not what you want anyway, it's no big loss, right? On Sun, Aug 3, 2008 at 7:59 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > The [EMAIL PROTECTED] email address is invalid. I tried to send it this > email: > > If I remember correctly, the news feed is gatewayed off the mailing > list, so it's possible for a message to the list to not appear in the > group if it gets dropped at the gateway. Sorry if this is redundant > info for you. > > If you don't wish to receive spam on your regular email address > (understandable) then either switch to reading and posting with a > gmail account (got lots of invites, just ask) or set up a separate > account to receive emails from pgsql and set up spam assassin and (/ > or) a couple of whitelists and use that. It's kinda rude to ask me a > question on a mailing list with an email address I can't respond to. > I spend time writing up an answer that only you needed to see, and > then can't send it to YOU, but only the whole list. > > On Sun, Aug 3, 2008 at 8:03 AM, Lew <[EMAIL PROTECTED]> wrote: >> Sim Zacks wrote: >>> >>> (quoting someone:) That LIKE operator is probably your problem. An unbounded LIKE like that (with a wildcard on both sides) means no index can be used, hence you get a sequential scan. >> >> Was the message to which you responded posted to the newsgroup? It isn't >> appearing in my newsreader. >> >> Who wrote the message you quoted (you failed to cite the source)? >> >> -- >> Lew >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [OT] newsreader issue? (Was: bytea encode performance issues)
On Sun, Aug 3, 2008 at 8:01 PM, Andrej Ricnik-Bay <[EMAIL PROTECTED]> wrote: > On 04/08/2008, Lew <[EMAIL PROTECTED]> wrote: > >> Was the message to which you responded posted to the newsgroup? It isn't >> appearing in my newsreader. >> >> Who wrote the message you quoted (you failed to cite the source)? > He was quoting Alban Hertroys, and it appeared on the general mailing list > (I didn't even know there was a Newsgroup). Wow! So, maybe everybody did need to see then. Still... > There seems to be a problem with your mail address, however ... ;} -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [OT] newsreader issue? (Was: bytea encode performance issues)
On 04/08/2008, Scott Marlowe <[EMAIL PROTECTED]> wrote: > One last thing. I'd rather my emails just get dropped silently if > that's the minimum someone can do. Use a valid email address that > goes to /dev/null and I'll be happy. You may miss a few things sent > directly to you, but since that's not what you want anyway, it's no > big loss, right? Aye ... dodgy spam-protection methods like that really suck. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [OT] newsreader issue? (Was: bytea encode performance issues)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I got the response by email, but it was also addressed to the mailing list. My response was also an email sent to the mailing list, not to the newsgroup. I got one other response, that I do not see in the newsgroup. And I will reply to it also using the mailing list. Sim Lew wrote: > Sim Zacks wrote: >> (quoting someone:) >>> That LIKE operator is probably your problem. An unbounded LIKE like that >>> (with a wildcard on both sides) means no index can be used, hence you >>> get a sequential scan. > > Was the message to which you responded posted to the newsgroup? It > isn't appearing in my newsreader. > > Who wrote the message you quoted (you failed to cite the source)? > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkiWk20ACgkQjDX6szCBa+pwzwCffdE3KZAg0f2TjUiq+gFCOrML HM4An2wcV9G9aAE+94DH6Vwc6deMIHB4 =N0v/ -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bytea encode performance issues
Tom Lane wrote:
> Could we see EXPLAIN ANALYZE, not EXPLAIN? Without actual facts
> to work from, any suggestions would be mere guesswork.
This was taken immediately after a vacuum analyze on the database.
"HashAggregate (cost=41596.68..41596.84 rows=16 width=764) (actual
time=488263.802..488263.837 rows=40 loops=1)"
" -> Nested Loop (cost=0.00..41596.60 rows=16 width=764) (actual
time=23375.445..488260.311 rows=40 loops=1)"
"-> Nested Loop (cost=0.00..41463.32 rows=16 width=780)
(actual time=23375.344..488231.994 rows=40 loops=1)"
" -> Seq Scan on dbmail_messageblks k
(cost=0.00..39193.21 rows=259 width=764) (actual time=30.662..486585.126
rows=2107 loops=1)"
"Filter: ((is_header = 0::smallint) AND
(encode(messageblk, 'escape'::text) ~~ '%Yossi%'::text))"
" -> Index Scan using dbmail_messages_2 on dbmail_messages
m (cost=0.00..8.75 rows=1 width=16) (actual time=0.777..0.777 rows=0
loops=2107)"
"Index Cond: (m.physmessage_id = k.physmessage_id)"
"Filter: ((mailbox_idnr = 8) AND (status = ANY
('{0,1}'::integer[])))"
"-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage p (cost=0.00..8.32 rows=1 width=8) (actual
time=0.701..0.703 rows=1 loops=40)"
" Index Cond: (k.physmessage_id = p.id)"
"Total runtime: 488264.192 ms"
> Also, what can you tell us about the sizes of the messageblk
> strings (max and avg would be interesting)?
>
select max(length(messageblk)),avg(length(messageblk)) from
dbmail_messageblks
MAXAVG
532259;48115.630147120314
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
