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 |/
