From: | Rajesh Kumar Mallah <mallah(dot)rajesh(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: order by slowing down a query by 80 times |
Date: | 2010-06-28 19:56:50 |
Message-ID: | AANLkTiklzIhL9g-Jek-o1QoSLM429ArQoWzHeg16yNez@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
The way to make this go faster is to set up the actually recommended
> infrastructure for full text search, namely create an index on
> (co_name_vec)::tsvector (either directly or using an auxiliary tsvector
> column). If you don't want to maintain such an index, fine, but don't
> expect full text search queries to be quick.
>
> regards, tom lane
>
Dear Tom/List ,
co_name_vec is actually the auxiliary tsvector column that is mantained via
a
an update trigger. and the index that you suggested is there . consider
simplified
version. When we order by co_name the index on co_name_vec is not used
some other index is used.
tradein_clients=> explain analyze SELECT profile_id from
general.profile_master b where 1=1 and co_name_vec @@ to_tsquery
('manufacturer') order by co_name limit 25;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3958.48 rows=25 width=25) (actual time=0.045..19.847
rows=25 loops=1)
-> Index Scan using profile_master_co_name on profile_master b
(cost=0.00..1125315.59 rows=7107 width=25) (actual time=0.043..19.818
rows=25 loops=1)
Filter: ((co_name_vec)::tsvector @@
to_tsquery('manufacturer'::text))
Total runtime: 19.894 ms
(4 rows)
tradein_clients=> explain analyze SELECT profile_id from
general.profile_master b where 1=1 and co_name_vec @@ to_tsquery
('manufacturer') limit 25;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..101.18 rows=25 width=4) (actual time=0.051..0.632
rows=25 loops=1)
-> Index Scan using profile_master_co_name_vec on profile_master b
(cost=0.00..28761.89 rows=7107 width=4) (actual time=0.049..0.593 rows=25
loops=1)
Index Cond: ((co_name_vec)::tsvector @@
to_tsquery('manufacturer'::text))
Total runtime: 0.666 ms
(4 rows)
tradein_clients=>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-06-28 20:18:58 | Re: order by slowing down a query by 80 times |
Previous Message | Rajesh Kumar Mallah | 2010-06-28 19:03:09 | Re: order by slowing down a query by 80 times |