It seems pretty much bigint to me :)
Indeed i was reluctant to remove the LIMIT clause but it seems for now
that there is no other option. There hasn't been any ANALYZE statements
at all (we migrated this db from another one and encountered the
problem afterwards); but quite frankly i would still regard it as a bug
if i need to run an ANALYZE statement first for such a simple query ;-)
Below is output describing messageblks table, all indices and finally
the two messageblk indices.
regards roel
dbmail=> \d messageblks
Table "messageblks"
Column | Type | Modifiers
-----------------+--------
+-------------------------------------------------------
messageblk_idnr | bigint | not null default
nextval('messageblk_idnr_seq'::text)
message_idnr | bigint | not null default '0'
messageblk | text | not null
blocksize | bigint | not null default '0'
Indexes: messageblks_msg_idx
Primary key: messageblks_pkey
Unique keys: messageblks_id_idx
dbmail=> \di
List of relations
Name | Type | Owner
-----------------------+-------+--------
aliases_alias_idx | index | dbmail
aliases_alias_low_idx | index | dbmail
aliases_pkey | index | dbmail
mailboxes_id_idx | index | dbmail
mailboxes_owner_idx | index | dbmail
mailboxes_pkey | index | dbmail
messageblks_id_idx | index | dbmail
messageblks_msg_idx | index | dbmail
messageblks_pkey | index | dbmail
messages_id_idx | index | dbmail
messages_mailbox_idx | index | dbmail
messages_pkey | index | dbmail
users_id_idx | index | dbmail
users_name_idx | index | dbmail
users_pkey | index | dbmail
(15 rows)
dbmail=> \d messageblks_id_idx
Index "messageblks_id_idx"
Column | Type
-----------------+--------
messageblk_idnr | bigint
unique btree
dbmail=> \d messageblks_msg_idx
Index "messageblks_msg_idx"
Column | Type
--------------+--------
message_idnr | bigint
btree
Philip Warner heeft op donderdag, 21 nov 2002 om 00:30
(Europe/Amsterdam) het volgende geschreven:
At 04:29 PM 20/11/2002 +0100, Roel Rozendaal - IC&S wrote:
dbmail=> explain SELECT messageblk FROM messageblks WHERE
message_idnr = 100::bigint ORDER BY messageblk_idnr limit 1;
\
Limit (cost=0.00..777.50 rows=1 width=40)
-> Index Scan using messageblks_id_idx on messageblks
(cost=0.00..1129984.15 rows=1453 width=40)
EXPLAIN
dbmail=> explain SELECT messageblk FROM messageblks WHERE
message_idnr = 100::bigint ORDER BY messageblk_idnr ;
NOTICE: QUERY PLAN:
Sort (cost=5793.33..5793.33 rows=1453 width=40)
-> Index Scan using messageblks_msg_idx on messageblks
(cost=0.00..5716.99 rows=1453 width=40)
From this I would guess that the data type of messageblks.message_idnr
is not BIGINT (which seems unlikely!). Can you do a "\d messageblks"?
Also, have you run an ANALYZE recently? And what version of PG are you
running?
I think it would be a bad idea to remove the LIMIT 1 in general since
it will cause the backend process to retrieve the entire message (not
good for big messages). But it is obviously necessary to avoid a
sequential scan of the messageblks table!
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
