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" :-)
