pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTsearch2 - bad performance with concatenated ts-vectors
4+ messages / 4 participants
[nested] [flat]
* Tsearch2 - bad performance with concatenated ts-vectors
@ 2011-08-02 06:22 Jan Wielgus <jan_w@tlen.pl>
2011-08-02 23:01 ` Re: Tsearch2 - bad performance with concatenated ts-vectors Gavin Flower <GavinFlower@archidevsys.co.nz>
2011-10-24 19:31 ` Re: Tsearch2 - bad performance with concatenated ts-vectors Robert Haas <robertmhaas@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Jan Wielgus @ 2011-08-02 06:22 UTC (permalink / raw)
To: pgsql-performance
Hello everyone,
This is my first post on this list, I tried to look after possible solutions in the archive, as well as in google, but I could not find an explanation for such a specific situation.
I am facing a performance problem connected with Postgres Tsearch2 FTS mechanism.
Here is my query:
select participant.participant_id from participant participant
join person person on person.person_participant_id = participant.participant_id
left join registration registration on registration.registration_registered_participant_id = participant.participant_id
left join enrollment enrollment on registration.registration_enrollment_id = enrollment.enrollment_id
join registration_configuration registration_configuration on enrollment.enrollment_configuration_id = registration_configuration.configuration_id
left join event_context context on context.context_id = registration_configuration.configuration_context_id
where participant.participant_type = 'PERSON'
and participant_status = 'ACTIVE'
and context.context_code in ('GB2TST2010A')
and registration_configuration.configuration_type in ('VISITOR')
and registration_configuration.configuration_id is not null
and participant.participant_tsv || person.person_tsv @@ to_tsquery('simple',to_tsquerystring('Abigail'))
limit 100
As you see, I am using two vectors which I concatenate and check against a tsquery.
Both vectors are indexed with GIN and updated with respective triggers in the following way:
ALTER TABLE person ALTER COLUMN person_tsv SET STORAGE EXTENDED;
CREATE INDEX person_ft_index ON person USING gin(person_tsv);
CREATE OR REPLACE FUNCTION update_person_tsv() RETURNS trigger AS $$ BEGIN NEW.person_tsv := to_tsvector('simple',create_tsv( ARRAY[NEW.person_first_name, NEW.person_last_name, NEW.person_middle_name] )); RETURN NEW; END; $$ LANGUAGE 'plpgsql';
CREATE TRIGGER person_tsv_update BEFORE INSERT or UPDATE ON person FOR EACH ROW EXECUTE PROCEDURE update_person_tsv();
ALTER TABLE participant ALTER COLUMN participant_tsv SET STORAGE EXTENDED;
CREATE INDEX participant_ft_index ON participant USING gin(participant_tsv);
CREATE OR REPLACE FUNCTION update_participant_tsv() RETURNS trigger AS $$ BEGIN NEW.participant_tsv := to_tsvector('simple',create_tsv( ARRAY[NEW.participant_login, NEW.participant_email] )); RETURN NEW; END; $$ LANGUAGE 'plpgsql';
CREATE TRIGGER participant_tsv_update BEFORE INSERT or UPDATE ON participant FOR EACH ROW EXECUTE PROCEDURE update_participant_tsv();
The database is quite big - has almost one million of participant records. The above query has taken almost 67 seconds to execute and fetch 100 rows, which is unacceptable for us.
As I assume, the problem is, when the vectors are concatenated, the individual indexes for each vector are not used. The execution plan done after 1st execution of the query:
"Limit (cost=46063.13..93586.79 rows=100 width=4) (actual time=4963.620..39703.645 rows=100 loops=1)"
" -> Nested Loop (cost=46063.13..493736.04 rows=942 width=4) (actual time=4963.617..39703.349 rows=100 loops=1)"
" Join Filter: (registration_configuration.configuration_id = enrollment.enrollment_configuration_id)"
" -> Nested Loop (cost=46063.13..493662.96 rows=3769 width=8) (actual time=4963.517..39701.557 rows=159 loops=1)"
" -> Nested Loop (cost=46063.13..466987.33 rows=3769 width=8) (actual time=4963.498..39698.542 rows=159 loops=1)"
" -> Hash Join (cost=46063.13..430280.76 rows=4984 width=8) (actual time=4963.464..39692.676 rows=216 loops=1)"
" Hash Cond: (participant.participant_id = person.person_participant_id)"
" Join Filter: ((participant.participant_tsv || person.person_tsv) @@ to_tsquery('simple'::regconfig, to_tsquerystring('Abigail'::text)))"
" -> Seq Scan on participant (cost=0.00..84680.85 rows=996741 width=42) (actual time=0.012..3132.944 rows=1007151 loops=1)"
" Filter: (((participant_type)::text = 'PERSON'::text) AND ((participant_status)::text = 'ACTIVE'::text))"
" -> Hash (cost=25495.39..25495.39 rows=1012539 width=38) (actual time=3145.628..3145.628 rows=1007151 loops=1)"
" Buckets: 2048 Batches: 128 Memory Usage: 556kB"
" -> Seq Scan on person (cost=0.00..25495.39 rows=1012539 width=38) (actual time=0.062..1582.990 rows=1007151 loops=1)"
" -> Index Scan using idx_registration_registered_participant_id on registration (cost=0.00..7.35 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=216)"
" Index Cond: (registration.registration_registered_participant_id = person.person_participant_id)"
" -> Index Scan using enrollment_pkey on enrollment (cost=0.00..7.07 rows=1 width=8) (actual time=0.011..0.013 rows=1 loops=159)"
" Index Cond: (enrollment.enrollment_id = registration.registration_enrollment_id)"
" -> Materialize (cost=0.00..16.55 rows=1 width=4) (actual time=0.002..0.005 rows=2 loops=159)"
" -> Nested Loop (cost=0.00..16.55 rows=1 width=4) (actual time=0.056..0.077 rows=2 loops=1)"
" Join Filter: (registration_configuration.configuration_context_id = context.context_id)"
" -> Index Scan using idx_configuration_type on registration_configuration (cost=0.00..8.27 rows=1 width=8) (actual time=0.018..0.022 rows=3 loops=1)"
" Index Cond: ((configuration_type)::text = 'VISITOR'::text)"
" Filter: (configuration_id IS NOT NULL)"
" -> Index Scan using idx_event_context_code on event_context context (cost=0.00..8.27 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=3)"
" Index Cond: ((context.context_code)::text = 'GB2TST2010A'::text)"
"Total runtime: 39775.578 ms"
The assumption seems to be correct, no indexes on vectors are used - sequence scans are done instead:
Join Filter: ((participant.participant_tsv || person.person_tsv) @@ to_tsquery('simple'::regconfig, to_tsquerystring('Abigail'::text)))"
" -> Seq Scan on participant (cost=0.00..84680.85 rows=996741 width=42) (actual time=0.012..3132.944 rows=1007151 loops=1)"
" Filter: (((participant_type)::text = 'PERSON'::text) AND ((participant_status)::text = 'ACTIVE'::text))"
" -> Hash (cost=25495.39..25495.39 rows=1012539 width=38) (actual time=3145.628..3145.628 rows=1007151 loops=1)"
" Buckets: 2048 Batches: 128 Memory Usage: 556kB"
" -> Seq Scan on person (cost=0.00..25495.39 rows=1012539 width=38) (actual time=0.062..1582.990 rows=1007151 loops=1)"
After I removed one of the vectors from the query and used only a single vector
...
and person.person_tsv @@ to_tsquery('simple', to_tsquery('simple',to_tsquerystring('Abigail'))
...
then the execution was much faster - about 5 seconds
Plan afterwards:
"Limit (cost=41.14..8145.82 rows=100 width=4) (actual time=3.776..13.454 rows=100 loops=1)"
" -> Nested Loop (cost=41.14..21923.77 rows=270 width=4) (actual time=3.773..13.248 rows=100 loops=1)"
" -> Nested Loop (cost=41.14..19730.17 rows=270 width=8) (actual time=3.760..11.971 rows=100 loops=1)"
" Join Filter: (registration_configuration.configuration_id = enrollment.enrollment_configuration_id)"
" -> Nested Loop (cost=0.00..16.55 rows=1 width=4) (actual time=0.051..0.051 rows=1 loops=1)"
" Join Filter: (registration_configuration.configuration_context_id = context.context_id)"
" -> Index Scan using idx_configuration_type on registration_configuration (cost=0.00..8.27 rows=1 width=8) (actual time=0.020..0.022 rows=2 loops=1)"
" Index Cond: ((configuration_type)::text = 'VISITOR'::text)"
" Filter: (configuration_id IS NOT NULL)"
" -> Index Scan using idx_event_context_code on event_context context (cost=0.00..8.27 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=2)"
" Index Cond: ((context.context_code)::text = 'GB2TST2010A'::text)"
" -> Nested Loop (cost=41.14..19700.12 rows=1080 width=12) (actual time=3.578..11.431 rows=269 loops=1)"
" -> Nested Loop (cost=41.14..12056.27 rows=1080 width=12) (actual time=3.568..8.203 rows=269 loops=1)"
" -> Bitmap Heap Scan on person (cost=41.14..3687.07 rows=1080 width=4) (actual time=3.553..4.401 rows=346 loops=1)"
" Recheck Cond: (person_tsv @@ to_tsquery('simple'::regconfig, to_tsquerystring('Abigail'::text)))"
" -> Bitmap Index Scan on person_ft_index (cost=0.00..40.87 rows=1080 width=0) (actual time=3.353..3.353 rows=1060 loops=1)"
" Index Cond: (person_tsv @@ to_tsquery('simple'::regconfig, to_tsquerystring('Abigail'::text)))"
" -> Index Scan using idx_registration_registered_participant_id on registration (cost=0.00..7.74 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=346)"
" Index Cond: (registration.registration_registered_participant_id = person.person_participant_id)"
" -> Index Scan using enrollment_pkey on enrollment (cost=0.00..7.07 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=269)"
" Index Cond: (enrollment.enrollment_id = registration.registration_enrollment_id)"
" -> Index Scan using participant_pkey on participant (cost=0.00..8.11 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=100)"
" Index Cond: (participant.participant_id = person.person_participant_id)"
" Filter: (((participant.participant_type)::text = 'PERSON'::text) AND ((participant.participant_status)::text = 'ACTIVE'::text))"
"Total runtime: 13.858 ms"
Now the index on vector was used:
"Recheck Cond: (person_tsv @@ to_tsquery('simple'::regconfig, to_tsquerystring('Abigail'::text)))"
" -> Bitmap Index Scan on person_ft_index (cost=0.00..40.87 rows=1080 width=0) (actual time=3.353..3.353 rows=1060 loops=1)"
" Index Cond: (person_tsv @@ to_tsquery('simple'::regconfig, to_tsquerystring('Abigail'::text)))"
So, there is apparently a problem with vector concatenating - the indexes don't work then. I tried to use the vectors separately and to make 'OR' comparison between single vector @@ ts_query checks,
but it didn't help very much (performance was better, but still over 20 sec):
...
(participant.participant_tsv @@ to_tsquery('simple',to_tsquerystring('Abigail'))) OR (person.person_tsv @@ to_tsquery('simple',to_tsquerystring('Abigail')))
...
Is there a way to make this work with better performance? Or is it necessary to create a single vector that contains data from multiple tables and then add an index on it? It would be so far problematic for us,
because we are using multiple complex queries with variable number of selected columns. I know that another solution might be an union among multiple queries, every of which uses a single vector,
but this solution is inconvenient too.
Greetings
Jan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Tsearch2 - bad performance with concatenated ts-vectors
2011-08-02 06:22 Tsearch2 - bad performance with concatenated ts-vectors Jan Wielgus <jan_w@tlen.pl>
@ 2011-08-02 23:01 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2011-08-04 21:16 ` Re: Tsearch2 - bad performance with concatenated ts-vectors Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 4+ messages in thread
From: Gavin Flower @ 2011-08-02 23:01 UTC (permalink / raw)
To: Jan Wielgus <jan_w@tlen.pl>; +Cc: pgsql-performance
On 02/08/11 18:22, Jan Wielgus wrote:
> select participant.participant_id from participant participant
> join person person on person.person_participant_id = participant.participant_id
> left join registration registration on registration.registration_registered_participant_id = participant.participant_id
> left join enrollment enrollment on registration.registration_enrollment_id = enrollment.enrollment_id
> join registration_configuration registration_configuration on enrollment.enrollment_configuration_id = registration_configuration.configuration_id
> left join event_context context on context.context_id = registration_configuration.configuration_context_id
> where participant.participant_type = 'PERSON'
> and participant_status = 'ACTIVE'
> and context.context_code in ('GB2TST2010A')
> and registration_configuration.configuration_type in ('VISITOR')
> and registration_configuration.configuration_id is not null
> and participant.participant_tsv || person.person_tsv @@ to_tsquery('simple',to_tsquerystring('Abigail'))
> limit 100
I am experimenting with formatting styles, especially relating to
joins. Because I have poor eyesight: visual clues are important, so
that I can focus on key points. Hence the use of abbreviations, naming
conventions, and careful indenting. (I found this especially
important, when I had to write a stored procedure with some 3K lines of
Sybase TransactSQL!) I also use uppercase key words, but I have not
bothered here.
So I would like people's opinions on how I have reformatted the above.
select
participant.participant_id
from
participant pa
join person pe
on pe.person_participant_id = pa.participant_id
left join registration re
on re.registration_registered_participant_id = pa.participant_id
left join enrollment en
on re.registration_enrollment_id = en.enrollment_id
join registration_configuration rc
on en.enrollment_configuration_id = rc.configuration_id
left join event_context ec
on ec.context_id = rc.configuration_context_id
where
pa.participant_type = 'PERSON' and
pa.participant_status = 'ACTIVE' and
ec.context_code in ('GB2TST2010A') and
rc.configuration_type in ('VISITOR') and
rc.configuration_id is not null and
pa.participant_tsv || pe.person_tsv @@
to_tsquery('simple',to_tsquerystring('Abigail'))
limit 100
Cheers,
Gavin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Tsearch2 - bad performance with concatenated ts-vectors
2011-08-02 06:22 Tsearch2 - bad performance with concatenated ts-vectors Jan Wielgus <jan_w@tlen.pl>
2011-08-02 23:01 ` Re: Tsearch2 - bad performance with concatenated ts-vectors Gavin Flower <GavinFlower@archidevsys.co.nz>
@ 2011-08-04 21:16 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2011-08-04 21:16 UTC (permalink / raw)
To: Gavin Flower <GavinFlower@archidevsys.co.nz>; Jan Wielgus <jan_w@tlen.pl>; +Cc: pgsql-performance
Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
> I am experimenting with formatting styles, especially relating to
> joins. Because I have poor eyesight: visual clues are important,
> so that I can focus on key points. Hence the use of
> abbreviations, naming conventions, and careful indenting.
> So I would like people's opinions on how I have reformatted [...]
This is a little off-topic for a performance list, but I have to
admit a lot of sympathy, since I have to do the same for similar
reasons. I found that keeping operators near the left margin and
always having matching parentheses be on the same line or in the
same column helps me tremendously. (This style tends not to be so
popular for younger coders with stronger eyes.)
I tend to go more this way:
select
participant.participant_id
from participant pa
join person pe
on pe.person_participant_id = pa.participant_id
left join registration re
on re.registration_registered_participant_id
= pa.participant_id
left join enrollment en
on re.registration_enrollment_id = en.enrollment_id
join registration_configuration rc
on en.enrollment_configuration_id = rc.configuration_id
left join event_context ec
on ec.context_id = rc.configuration_context_id
where pa.participant_type = 'PERSON'
and pa.participant_status = 'ACTIVE'
and ec.context_code in ('GB2TST2010A')
and rc.configuration_type in ('VISITOR')
and rc.configuration_id is not null
and pa.participant_tsv || pe.person_tsv
@@ to_tsquery('simple',to_tsquerystring('Abigail'))
limit 100
;
With multiple ON conditions I like to do this:
JOIN some_table st
ON ( st.this_column = ot.this_column
AND st.another_column = ot.another_column
AND ( st.feathers > ot.feathers
OR st.lead_ingots > ot.lead_ingots
)
)
I often line up the operators and column-names, too, if it seems
easier on the eyes.
The above won't look very sensible in a proportional font.
I doubt this will become a dominant coding convention; it's just
what works for me. ;-)
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Tsearch2 - bad performance with concatenated ts-vectors
2011-08-02 06:22 Tsearch2 - bad performance with concatenated ts-vectors Jan Wielgus <jan_w@tlen.pl>
@ 2011-10-24 19:31 ` Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Robert Haas @ 2011-10-24 19:31 UTC (permalink / raw)
To: Jan Wielgus <jan_w@tlen.pl>; +Cc: pgsql-performance
On Tue, Aug 2, 2011 at 2:22 AM, Jan Wielgus <jan_w@tlen.pl> wrote:
> So, there is apparently a problem with vector concatenating - the indexes don't work then. I tried to use the vectors separately and to make 'OR' comparison between single vector @@ ts_query checks,
> but it didn't help very much (performance was better, but still over 20 sec):
> ...
> (participant.participant_tsv @@ to_tsquery('simple',to_tsquerystring('Abigail'))) OR (person.person_tsv @@ to_tsquery('simple',to_tsquerystring('Abigail')))
> ...
>
> Is there a way to make this work with better performance? Or is it necessary to create a single vector that contains data from multiple tables and then add an index on it? It would be so far problematic for us,
> because we are using multiple complex queries with variable number of selected columns. I know that another solution might be an union among multiple queries, every of which uses a single vector,
> but this solution is inconvenient too.
Only something of the form 'indexed-column indexable-operator value'
is going to be indexable. So when you concatenate two columns from
different tables - as you say - not indexable.
In general, OR-based conditions that cross table boundaries tend to be
expensive, because they have to be applied only after performing the
join. You can't know for sure looking only at a row from one table
whether or not it will be needed, so you have to join them all and
then filter the results.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-10-24 19:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-02 06:22 Tsearch2 - bad performance with concatenated ts-vectors Jan Wielgus <jan_w@tlen.pl>
2011-08-02 23:01 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2011-08-04 21:16 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-10-24 19:31 ` Robert Haas <robertmhaas@gmail.com>
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