Re: Query Optimisation

Try explicitly casting the values into the appropriate date type.

Jon

On Tue, 15 Jul 2003 psql-mail(at)freeuk(dot)com wrote:

> 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)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>