Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 044F6632F7D for ; Mon, 28 Jun 2010 08:39:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 94300-03 for ; Mon, 28 Jun 2010 11:39:01 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com [74.125.82.46]) by mail.postgresql.org (Postfix) with ESMTP id 4D39A632704 for ; Mon, 28 Jun 2010 08:39:02 -0300 (ADT) Received: by wwi17 with SMTP id 17so2540806wwi.19 for ; Mon, 28 Jun 2010 04:39:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:cc:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=ySTusoZNxa9cSGLY5MRfN3czOpHgoQGj7n3MUW00lSo=; b=ir4CnZBmD7IYI8t8kfa4e+Aief5fgArKv5Ds9lV6mut7tz2OsTPKDmPjJ8C+DuTuY/ niOXN6QZYI4PjI/iXYRAnI/ieV221h9w0B46aKafpxrgKTVy9WICNdKnRVLZVLc+qete XQ/s/LAqIkYLtkpr86FSx/Ip03RFXBVs6HYnw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; b=tvvIzAD0Hrbyp8jsaH3Rk0HbyKtJDbzPWfxuX2xNmTf87nWeeFrQ7yHWAPjb93nR4S ERUB4Bgduspx4m5V7a6Q2L3qSEVib+DqbOvynnfCw2XfvcNbMZc5YZfK/dWFMrkWHgyt uPkJXPRnUyNBXkc+casOFp1Vsuaf9GofdrgNE= Received: by 10.216.170.83 with SMTP id o61mr3565919wel.37.1277725140158; Mon, 28 Jun 2010 04:39:00 -0700 (PDT) Received: from [0.0.0.0] (5357DA75.cable.casema.nl [83.87.218.117]) by mx.google.com with ESMTPS id o72sm4949227wej.15.2010.06.28.04.38.59 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 28 Jun 2010 04:38:59 -0700 (PDT) Message-ID: <4C2889EF.1050908@gmail.com> Date: Mon, 28 Jun 2010 13:39:27 +0200 From: Yeb Havinga User-Agent: Thunderbird 2.0.0.24 (Windows/20100228) MIME-Version: 1.0 To: Rajesh Kumar Mallah CC: pgsql-performance@postgresql.org Subject: Re: order by slowing down a query by 80 times References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.001 tagged_above=-10 required=5 tests=BAYES_40=-0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201006/497 X-Sequence-Number: 39353 Rajesh Kumar Mallah wrote: > Dear List, > > just by removing the order by co_name reduces the query time dramatically > from ~ 9 sec to 63 ms. Can anyone please help. The 63 ms query result is probably useless since it returns a limit of 25 rows from an unordered result. It is not surprising that this is fast. The pain is here: Index Scan using profile_master_co_name on profile_master b (cost=0.00..1125295.59 rows=6968 width=25) (actual time=0.097..9193.154 rows=2212 loops=1) Filter: ((co_name IS NOT NULL) AND ((co_name_vec)::tsvector @@ to_tsquery('manufacturer'::text))) It looks like seq_scans are disabled, since the index scan has only a filter expression but not an index cond. regards, Yeb Havinga > > Regds > Rajesh Kumar Mallah. > > > explain analyze SELECT * from ( SELECT > a.profile_id,a.userid,a.amount,a.category_id,a.catalog_id,a.keywords,b.co_name > from general.catalogs a join general.profile_master using(profile_id) > where 1=1 and co_name_vec @@ to_tsquery('manufacturer') and > b.co_name is not null and a.ifmain is true ) as c order by co_name > limit 25 offset 0; > > > Limit (cost=0.00..3659.13 rows=25 width=129) (actual > time=721.075..9241.105 rows=25 loops=1) > -> Nested Loop (cost=0.00..1215772.28 rows=8307 width=476) > (actual time=721.073..9241.050 rows=25 loops=1) > -> Nested Loop (cost=0.00..1208212.37 rows=8307 width=476) > (actual time=721.052..9240.037 rows=25 loops=1) > -> Nested Loop (cost=0.00..1204206.26 rows=6968 > width=472) (actual time=721.032..9239.516 rows=25 loops=1) > -> Nested Loop (cost=0.00..1154549.19 rows=6968 > width=471) (actual time=721.012..9236.523 rows=25 loops=1) > -> Index Scan using profile_master_co_name > on profile_master b (cost=0.00..1125295.59 rows=6968 width=25) > (actual time=0.097..9193.154 rows=2212 loops=1) > Filter: ((co_name IS NOT NULL) AND > ((co_name_vec)::tsvector @@ to_tsquery('manufacturer'::text))) > -> Index Scan using > catalog_master_profile_id_fkindex on catalog_master (cost=0.00..4.19 > rows=1 width=446) (actual time=0.016..0.016 rows=0 loops=2212) > Index Cond: > (catalog_master.profile_id = b.profile_id) > Filter: ((catalog_master.hide IS > FALSE) AND ((catalog_master.hosting_status)::text = 'ACTIVE'::text)) > -> Index Scan using > profile_master_profile_id_pkey on profile_master (cost=0.00..7.11 > rows=1 width=9) (actual time=0.105..0.105 rows=1 loops=25) > Index Cond: (profile_master.profile_id = > catalog_master.profile_id) > -> Index Scan using > catalog_categories_pkey_catalog_id_category_id on catalog_categories > (cost=0.00..0.56 rows=1 width=8) (actual time=0.014..0.015 rows=1 > loops=25) > Index Cond: (catalog_categories.catalog_id = > catalog_master.catalog_id) > Filter: (catalog_categories.ifmain IS TRUE) > -> Index Scan using web_category_master_pkey on > web_category_master (cost=0.00..0.90 rows=1 width=4) (actual > time=0.034..0.036 rows=1 loops=25) > Index Cond: (web_category_master.category_id = > catalog_categories.category_id) > Filter: ((web_category_master.status)::text = > 'ACTIVE'::text) > Total runtime: 9241.304 ms > > explain analyze SELECT * from ( SELECT > a.profile_id,a.userid,a.amount,a.category_id,a.catalog_id,a.keywords,b.co_name > from general.catalogs a join general.profile_master b > using(profile_id) where 1=1 and co_name_vec @@ > to_tsquery('manufacturer') and b.co_name is not null and a.ifmain is > true ) as c limit 25 offset 0; > > QUERY PLAN > > ---------------------------------------------------------------------------------------------- > Limit (cost=0.00..358.85 rows=25 width=476) (actual > time=0.680..63.176 rows=25 loops=1) > -> Nested Loop (cost=0.00..119238.58 rows=8307 width=476) (actual > time=0.677..63.139 rows=25 loops=1) > -> Nested Loop (cost=0.00..111678.66 rows=8307 width=476) > (actual time=0.649..62.789 rows=25 loops=1) > -> Nested Loop (cost=0.00..107672.56 rows=6968 > width=472) (actual time=0.626..62.436 rows=25 loops=1) > -> Nested Loop (cost=0.00..58015.49 rows=6968 > width=471) (actual time=0.606..62.013 rows=25 loops=1) > -> Index Scan using > profile_master_co_name_vec on profile_master b (cost=0.00..28761.89 > rows=6968 width=25) (actual time=0.071..50.576 rows=1160 loops=1) > Index Cond: ((co_name_vec)::tsvector > @@ to_tsquery('manufacturer'::text)) > Filter: (co_name IS NOT NULL) > -> Index Scan using > catalog_master_profile_id_fkindex on catalog_master (cost=0.00..4.19 > rows=1 width=446) (actual time=0.008..0.008 rows=0 loops=1160) > Index Cond: > (catalog_master.profile_id = b.profile_id) > Filter: ((catalog_master.hide IS > FALSE) AND ((catalog_master.hosting_status)::text = 'ACTIVE'::text)) > -> Index Scan using > profile_master_profile_id_pkey on profile_master (cost=0.00..7.11 > rows=1 width=9) (actual time=0.012..0.012 rows=1 loops=25) > Index Cond: (profile_master.profile_id = > catalog_master.profile_id) > -> Index Scan using > catalog_categories_pkey_catalog_id_category_id on catalog_categories > (cost=0.00..0.56 rows=1 width=8) (actual time=0.010..0.011 rows=1 > loops=25) > Index Cond: (catalog_categories.catalog_id = > catalog_master.catalog_id) > Filter: (catalog_categories.ifmain IS TRUE) > -> Index Scan using web_category_master_pkey on > web_category_master (cost=0.00..0.90 rows=1 width=4) (actual > time=0.009..0.010 rows=1 loops=25) > Index Cond: (web_category_master.category_id = > catalog_categories.category_id) > Filter: ((web_category_master.status)::text = > 'ACTIVE'::text) > Total runtime: 63.378 ms > >