Autocompletion with full text search

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

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).

You will find below additional information to bring context to my question.
Thank you in advance for your help.

Here is the schema of the table :

CREATE TABLE patients (
    id integer NOT NULL,
    first_name character varying(255),
    last_name character varying(255),
    regular_doctor_name character varying(255),
    regular_doctor_city character varying(255),
    email character varying(255),
    phone_number character varying(255),
    secondary_phone_number character varying(255),
    gender boolean,
    birthdate date,
    zipcode character varying(255),
    city character varying(255),
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    imported_at timestamp without time zone,
    import_error text,
    import_identifier character varying(255),
    address character varying(255),
    deleted_at timestamp without time zone,
    account_id integer,
    main boolean DEFAULT false NOT NULL,
    insurance_type character varying(255),
    patient_base_ids_cache integer[] DEFAULT '{}'::integer[],
    crucial_info character varying(255),
    referrer character varying(255),
    occupation character varying(255),
    custom_fields_values hstore DEFAULT ''::hstore NOT NULL,
    bounced_at timestamp without time zone,
    merged_at timestamp without time zone,
    maiden_name character varying(255)
);

Here is the dictionary definition we used for full text search :

CREATE TEXT SEARCH CONFIGURATION custom_name_search (
    PARSER = pg_catalog."default" );

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR asciiword WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR word WITH unaccent, simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR numword WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR email WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR url WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR host WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR sfloat WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR version WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR hword_numpart WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR hword_part WITH unaccent, simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR hword_asciipart WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR numhword WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR asciihword WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR hword WITH unaccent, simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR url_path WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR file WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR "float" WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR "int" WITH simple;

ALTER TEXT SEARCH CONFIGURATION custom_name_search
    ADD MAPPING FOR uint WITH simple;

Here are the indexes on the patients table :

CREATE INDEX index_patients_on_account_id ON patients USING btree (account_id);
CREATE INDEX index_patients_on_import_identifier ON patients USING btree (import_identifier);
CREATE INDEX index_patients_on_patient_base_ids_cache ON patients USING gin (patient_base_ids_cache);
CREATE INDEX index_patients_on_phone_number ON patients USING btree (phone_number);
CREATE INDEX patients_clean_secondary_phone_number_index ON patients USING btree (replace((secondary_phone_number)::text, ' '::text, ''::text));
CREATE INDEX tsvector_on_patients ON patients USING gin (to_tsvector('custom_name_search'::regconfig, (((COALESCE(last_name, ''::character varying))::text || ' '::text) || (COALESCE(first_name, ''::character varying))::text)));
CREATE INDEX tsvector_on_patients_and_patient_base_ids_cache ON patients USING gin (to_tsvector('custom_name_search'::regconfig, (((COALESCE(last_name, ''::character varying))::text || ' '::text) || (COALESCE(first_name, ''::character varying))::text)), patient_base_ids_cache);
CREATE INDEX tsvector_on_patients_first_name ON patients USING gin (to_tsvector('custom_name_search'::regconfig, (COALESCE(first_name, ''::character varying))::text));
CREATE INDEX tsvector_on_patients_first_name_and_patient_base_ids_cache ON patients USING gin (to_tsvector('custom_name_search'::regconfig, (COALESCE(first_name, ''::character varying))::text), patient_base_ids_cache);
CREATE INDEX tsvector_on_patients_last_name_and_patient_base_ids_cache ON patients USING gin (to_tsvector('custom_name_search'::regconfig, (COALESCE(last_name, ''::character varying))::text), patient_base_ids_cache);


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)



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux