Skip site navigation (1) Skip section navigation (2)

Re: order by slowing down a query by 80 times

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 (view raw or flat)
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=>

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2010-06-28 20:18:58
Subject: Re: order by slowing down a query by 80 times
Previous:From: Rajesh Kumar MallahDate: 2010-06-28 19:03:09
Subject: Re: order by slowing down a query by 80 times

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group