Thread: Autocompletion with full text search
SELECT COUNT(id) FROM patients;
count
---------
6219569
(1 row)
SELECT version(); version
------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
(1 row)
=> SELECT name, current_setting(name), source
-> FROM pg_settings
-> WHERE source NOT IN ('default', 'override');
name | current_setting | source
--------------------------------+-------------------------------------------------------------------------------------------------------+----------------------
application_name | psql | client
archive_command | test -f /etc/postgresql/wal-e.d/ARCHIVING_OFF || envdir /etc/postgresql/wal-e.d/env wal-e wal-push %p | configuration file
archive_mode | on | configuration file
archive_timeout | 1min | configuration file
bytea_output | escape | user
checkpoint_completion_target | 0.7 | configuration file
checkpoint_segments | 40 | configuration file
checkpoint_timeout | 10min | configuration file
client_encoding | UTF8 | client
client_min_messages | notice | configuration file
cpu_index_tuple_cost | 0.001 | configuration file
cpu_operator_cost | 0.0005 | configuration file
cpu_tuple_cost | 0.003 | configuration file
DateStyle | ISO, MDY | configuration file
default_text_search_config | pg_catalog.english | configuration file
effective_cache_size | 10800000kB | configuration file
hot_standby | on | configuration file
hot_standby_feedback | on | configuration file
lc_messages | en_US.UTF-8 | configuration file
lc_monetary | en_US.UTF-8 | configuration file
lc_numeric | en_US.UTF-8 | configuration file
lc_time | en_US.UTF-8 | configuration file
listen_addresses | * | configuration file
local_preload_libraries | pgextwlist | configuration file
log_checkpoints | on | configuration file
log_connections | on | configuration file
log_destination | stderr | configuration file
log_line_prefix | %m %p %u [PINK] | configuration file
log_lock_waits | on | configuration file
log_min_duration_statement | 2s | configuration file
log_min_messages | notice | configuration file
log_rotation_age | 1d | configuration file
log_rotation_size | 100MB | configuration file
log_statement | ddl | configuration file
log_temp_files | 10MB | configuration file
log_timezone | UTC | configuration file
log_truncate_on_rotation | off | configuration file
logfebe.identity | c671acf1-c82e-4c2d-a3b3-f815580b6db5 | configuration file
logfebe.unix_socket | /tmp/pg_logplexcollector/pg_logplexcollector.sock | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 1700MB | configuration file
max_connections | 500 | configuration file
max_prepared_transactions | 0 | configuration file
max_stack_depth | 2MB | environment variable
max_standby_archive_delay | -1 | configuration file
max_standby_streaming_delay | -1 | configuration file
max_wal_senders | 20 | configuration file
port | 5432 | configuration file
random_page_cost | 2 | configuration file
shared_buffers | 2929MB | configuration file
ssl | on | configuration file
ssl_renegotiation_limit | 0 | configuration file
superuser_reserved_connections | 3 | configuration file
synchronous_commit | local | configuration file
synchronous_standby_names | follower | configuration file
temp_tablespaces | ephemeral | database
TimeZone | UTC | configuration file
track_io_timing | on | configuration file
wal_keep_segments | 61 | configuration file
wal_level | hot_standby | configuration file
work_mem | 100MB | configuration file
(61 rows)
On 1/15/15 6:41 PM, Ivan Schneider wrote: > > We implemented an autocompletion feature (case and accent insensitive) > using PostgreSQL full text search. > The query fetches patient ids matching the full text query that belong > to a given patient base (rows contain a pg_array with patient_base_ids). > Our table grew over time (6.2 million rows now) and the query got > slower. We are wondering if we have hit the limit or if there is still > room for performance improvement with better indexing or data > partitioning for instance. > Here is a link to the "explain (analyze, buffers)" results from such a > query run on one of our servers : http://explain.depesz.com/s/a5Q9 > Running analyze on the table doesn't change the results and the table is > autovacuumed (last one was 2015-01-08 22:18). > The query time is consumed by scanning the index, which at 152ms doesn't seem all that bad. Have you tried reindexing? That might help. You could also try something like trigram (http://www.postgresql.org/docs/9.1/static/pgtrgm.html); it might be faster. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com