X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id DC620CC3CB for ; Tue, 15 Jul 2003 15:59:07 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 38969-01 for ; Tue, 15 Jul 2003 15:59:32 +0000 (GMT) Received: from buckaroo.freeuk.net (buckaroo.freeuk.net [212.126.144.5]) by svr1.postgresql.org (Postfix) with ESMTP id A9DD6CC3C7 for ; Tue, 15 Jul 2003 12:59:02 -0300 (ADT) Received: from claranet by buckaroo.freeuk.net with local (Exim 4.12) id 19cSDD-000Euo-00 for pgsql-general@postgresql.org; Tue, 15 Jul 2003 16:59:31 +0100 To: pgsql-general@postgresql.org Reply-To: psql-mail@freeuk.com Subject: Query Optimisation Cc: X-Remote_Addr: 212.126.153.41 Precedence: normal Message-Id: From: psql-mail@freeuk.com Date: Tue, 15 Jul 2003 16:59:31 +0100 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/560 X-Sequence-Number: 45413 Hi, I have the following query - is there anything i've missed or is it just slow?! I have an index on meta.date that i thought might have been used but isn't (I know it would only be a small performance increase in the current plan). meta.date is between 1999 and 2003. I think generally the most efficient order to do things would be to extract all the messages within the date range and then search over just them. I am currently in the process of setting up full text indexing as described in the techdocs.postgresql.org i guess this is the main way of speeding up searches through ~40GB of bulk text? Thanks!... m EXPLAIN ANALYZE SELECT meta.msg_id, date, from_line, subject FROM message ,meta WHERE meta.date >= '15-06-2003 00:00:00' AND meta.date <= '26-06-2003 00:00:00' AND message.header||message.body ILIKE '%chicken%' AND meta.sys_id = message.sys_id ORDER BY meta.date DESC; QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------ Sort (cost=50.16..50.17 rows=1 width=120) (actual time=412333.65.. 412333.76 rows=166 loops=1) Sort Key: meta.date -> Nested Loop (cost=0.00..50.15 rows=1 width=120) (actual time= 400713.41..412332.53 rows=166 loops=1) -> Seq Scan on message (cost=0.00..25.00 rows=5 width=8) (actual time= 58.18..410588.49 rows=20839 loops=1) Filter: ((header || body) ~~* '%chicken%'::text) -> Index Scan using meta_pkey on meta (cost=0.00..5.02 rows=1 width= 112) (actual time=0.07..0.07 rows=0 loops=20839) Index Cond: (meta.sys_id = "outer".sys_id) Filter: ((date >= '2003-06-15 00:00:00'::timestamp without time zone) AND (date <= '2003-06-26 00:00:00'::timestamp without time zone)) Total runtime: 412334.08 msec (9 rows)