pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Optimisation
4+ messages / 4 participants
[nested] [flat]
* Query Optimisation
@ 2003-07-15 15:59 psql-mail@freeuk.com
2003-07-15 16:09 ` Re: [GENERAL] Query Optimisation Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-15 16:21 ` Re: Query Optimisation Jonathan Bartlett <johnnyb@eskimo.com>
2003-07-15 16:35 ` Re: Query Optimisation Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 4+ messages in thread
From: psql-mail@freeuk.com @ 2003-07-15 15:59 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL] Query Optimisation
2003-07-15 15:59 Query Optimisation psql-mail@freeuk.com
@ 2003-07-15 16:09 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-07-15 16:09 UTC (permalink / raw)
To: psql-mail@freeuk.com; +Cc: pgsql-performance
[replying to -performance]
On Tue, 15 Jul 2003 psql-mail@freeuk.com wrote:
> Hi, I have the following query - is there anything i've missed or is it
> just slow?!
The fact that it underestimates the number of matching message rows by a
factor of about 4000 doesn't help. I'm not sure you're going to be able to
get a better estimate using message.header||message.body ILIKE '%chicken%'
(possibly using two ilikes with or might help but probably not enough).
Have you vacuum analyzed the two tables recently? The seq scan cost on
message seems fairly low given what I would expect to be the size of that
table.
> 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?
That's still probably the best way.
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query Optimisation
2003-07-15 15:59 Query Optimisation psql-mail@freeuk.com
@ 2003-07-15 16:21 ` Jonathan Bartlett <johnnyb@eskimo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Jonathan Bartlett @ 2003-07-15 16:21 UTC (permalink / raw)
To: psql-mail@freeuk.com; +Cc: pgsql-general@postgresql.org
Try explicitly casting the values into the appropriate date type.
Jon
On Tue, 15 Jul 2003 psql-mail@freeuk.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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query Optimisation
2003-07-15 15:59 Query Optimisation psql-mail@freeuk.com
@ 2003-07-15 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-07-15 16:35 UTC (permalink / raw)
To: psql-mail@freeuk.com; +Cc: pgsql-general@postgresql.org
psql-mail@freeuk.com writes:
> -> 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)
Estimated cost of a seqscan only 25? Have you ever vacuumed or analyzed
that table? The planner evidently thinks it is tiny ...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-07-15 16:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-15 15:59 Query Optimisation psql-mail@freeuk.com
2003-07-15 16:09 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-15 16:21 ` Jonathan Bartlett <johnnyb@eskimo.com>
2003-07-15 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox