pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedImproving query performance
3+ messages / 2 participants
[nested] [flat]
* Improving query performance
@ 2007-03-02 04:00 David Leangen <postgres@leangen.net>
0 siblings, 1 reply; 3+ messages in thread
From: David Leangen @ 2007-03-02 04:00 UTC (permalink / raw)
To: pgsql-performance
Hello!
I'm new to performance tuning on postgres. I've read the docs on the
posgtres site, as well as:
http://www.revsys.com/writings/postgresql-performance.html
http://www.powerpostgresql.com/PerfList
However, my query is really slow, and I'm not sure what the main cause
could be, as there are so many variables. I'm hoping people with more
experience could help out.
My machine has 8Gb RAM, 2xCPU (2Gz, I think...)
Table has about 1M rows.
This is my postgres.conf:
listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 256000
effective_cache_size = 1000000
work_mem = 5000000
redirect_stderr = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 0
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
This is the result of "explain analyze":
Aggregate (cost=384713.17..384713.18 rows=1 width=4) (actual
time=254856.025..254856.025 rows=1 loops=1)
-> Seq Scan on medline_articles t0 (cost=0.00..382253.00
rows=984068 width=4) (actual time=511.841..254854.981 rows=788 loops=1)
Filter: long_ugly_query_here
And this is the actual query:
SELECT COUNT(t0.ID) FROM public.MY_TABLE t0
WHERE ((POSITION('adenosine cyclic 35-monophosphate' IN LOWER(t0.TITLE))
- 1) >=0 OR
(POSITION('adenosine cyclic 55-monophosphate' IN LOWER(t0.TEXT)) - 1) >=
0 OR
(POSITION('cyclic amp, disodium salt' IN LOWER(t0.TITLE)) - 1) >= 0 OR
(POSITION('cyclic amp, disodium salt' IN LOWER(t0.TEXT)) - 1) >= 0 OR
(POSITION('cyclic amp, sodium salt' IN LOWER(t0.TEXT)) - 1) >= 0 OR
(POSITION('cyclic amp, sodium salt' IN LOWER(t0.TITLE)) - 1) >= 0 OR
(POSITION('cyclic amp' IN LOWER(t0.TEXT)) - 1) >= 0 OR
(POSITION('cyclic amp' IN LOWER(t0.TITLE)) - 1) >= 0 OR
(POSITION('cyclic amp, monopotassium salt' IN LOWER(t0.TEXT)) - 1) >= 0
OR
(POSITION('cyclic amp, monopotassium salt' IN LOWER(t0.TEXT)) - 1) >= 0
OR
(POSITION('adenosine cyclic-35-monophosphate' IN LOWER(t0.TEXT)) - 1) >=
0 OR
(POSITION('adenosine cyclic-35-monophosphate' IN LOWER(t0.TITLE)) - 1)
>= 0 OR
(POSITION('adenosine cyclic monophosphate' IN LOWER(t0.TEXT)) - 1) >= 0
OR
(POSITION('adenosine cyclic monophosphate' IN LOWER(t0.TITLE)) - 1) >= 0
OR
(POSITION('cyclic amp, monoammonium salt' IN LOWER(t0.TEXT)) - 1) >= 0
OR
(POSITION('cyclic amp, monoammonium salt' IN LOWER(t0.TITLE)) - 1) >= 0
OR
(POSITION('adenosine cyclic 3,5 monophosphate' IN LOWER(t0.TEXT)) - 1)
>= 0 OR
(POSITION('adenosine cyclic 3,5 monophosphate' IN LOWER(t0.TITLE)) - 1)
>= 0 OR
(POSITION('cyclic amp, monosodium salt' IN LOWER(t0.TEXT)) - 1) >= 0 OR
(POSITION('cyclic amp, monosodium salt' IN LOWER(t0.TITLE)) - 1) >= 0
OR
(POSITION('cyclic amp, (r)-isomer' IN LOWER(t0.TEXT)) - 1) >= 0 OR
(POSITION('cyclic amp, (r)-isomer' IN LOWER(t0.TEXT)) - 1) >= 0)
Some more info:
pubmed=> SELECT relpages, reltuples FROM pg_class WHERE relname =
'MY_TABLE';
relpages | reltuples
----------+-----------
155887 | 984200
(1 row)
Thanks for any suggestions!
Dave
PS - Yes! I did run "vacuum analyze" :-)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Improving query performance
@ 2007-03-02 05:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Leangen <postgres@leangen.net>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-03-02 05:48 UTC (permalink / raw)
To: postgres@leangen.net; +Cc: pgsql-performance
David Leangen <postgres@leangen.net> writes:
> And this is the actual query:
> SELECT COUNT(t0.ID) FROM public.MY_TABLE t0
> WHERE ((POSITION('adenosine cyclic 35-monophosphate' IN LOWER(t0.TITLE))
> - 1) >=0 OR
> (POSITION('adenosine cyclic 55-monophosphate' IN LOWER(t0.TEXT)) - 1) >=
> 0 OR
> (POSITION('cyclic amp, disodium salt' IN LOWER(t0.TITLE)) - 1) >= 0 OR
> (POSITION('cyclic amp, disodium salt' IN LOWER(t0.TEXT)) - 1) >= 0 OR
> (POSITION('cyclic amp, sodium salt' IN LOWER(t0.TEXT)) - 1) >= 0 OR
> ...etc...
I think you need to look into full-text indexing (see tsearch2).
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Improving query performance
@ 2007-03-02 09:06 David Leangen <postgres@leangen.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: David Leangen @ 2007-03-02 09:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> > And this is the actual query:
>
> I think you need to look into full-text indexing (see tsearch2).
Thanks, Tom.
Yes, we know this.
This is just a temporary fix that we needed to get up today for biz
reasons. Implementing full-text searching within a few short hours was
out of the question.
Anyway, we found a temporary solution. We'll be doing this "properly"
later.
Thanks for taking the time to suggest this.
Cheers,
Dave
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-03-02 09:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-02 04:00 Improving query performance David Leangen <postgres@leangen.net>
2007-03-02 05:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-02 09:06 ` David Leangen <postgres@leangen.net>
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