X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 64BA952D61 for ; Thu, 14 Jul 2005 23:08:16 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 62506-04 for ; Fri, 15 Jul 2005 02:08:14 +0000 (GMT) Received: from hub.org (hub.org [200.46.204.220]) by svr1.postgresql.org (Postfix) with ESMTP id CAFD552D26 for ; Thu, 14 Jul 2005 23:08:14 -0300 (ADT) Received: from localhost (unknown [200.46.204.144]) by hub.org (Postfix) with ESMTP id 2D99CA246A4 for ; Thu, 14 Jul 2005 23:08:14 -0300 (ADT) Received: from hub.org ([200.46.204.220]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 62363-08 for ; Fri, 15 Jul 2005 02:08:13 +0000 (GMT) Received: from ganymede.hub.org (blk-224-176-51.eastlink.ca [24.224.176.51]) by hub.org (Postfix) with ESMTP id 831A6A246C4 for ; Thu, 14 Jul 2005 23:08:12 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id 707C33DEB3; Thu, 14 Jul 2005 23:08:23 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 66F3B3CEE4 for ; Thu, 14 Jul 2005 23:08:23 -0300 (ADT) X-Return-Path: X-Received: from localhost ([unix socket]) by ganymede.hub.org (Cyrus v2.2.12) with LMTPA; Wed, 13 Jul 2005 19:36:18 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 9C4B73846E for ; Wed, 13 Jul 2005 19:35:34 -0300 (ADT) X-Received: from mail.postgresql.org [200.46.204.71] by localhost with IMAP (fetchmail-6.2.5) for scrappy@localhost (single-drop); Wed, 13 Jul 2005 19:35:34 -0300 (ADT) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.12) with LMTPA; Wed, 13 Jul 2005 19:35:14 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EA83752C3B for ; Wed, 13 Jul 2005 19:35:13 -0300 (ADT) X-Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76830-09 for ; Wed, 13 Jul 2005 22:35:11 +0000 (GMT) X-Received: from nextmail.ru (unknown [83.222.5.149]) by svr1.postgresql.org (Postfix) with SMTP id D720952C2C for ; Wed, 13 Jul 2005 19:35:09 -0300 (ADT) X-Received: (qmail 44456 invoked from network); 13 Jul 2005 22:34:01 -0000 X-Received: from ppp85-140-125-114.pppoe.mtu-net.ru (HELO z) (85.140.125.114) by nextmail.ru with SMTP; 13 Jul 2005 22:34:01 -0000 From: "jobapply" To: Subject: Functional index is 5 times slower than the basic one Date: Thu, 14 Jul 2005 02:35:10 +0400 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 Thread-Index: AcWH+yAUOw6G+W3wThu3dWh4z4ubKA== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-Id: <20050713223509.D720952C2C@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-DCC: : X-Spam-Pyzor: Reported 0 times. ReSent-Date: Thu, 14 Jul 2005 23:08:18 -0300 (ADT) Resent-From: "Marc G. Fournier" Resent-To: pgsql-performance@postgresql.org ReSent-Subject: Functional index is 5 times slower than the basic one ReSent-Message-ID: <20050714230818.L66818@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at hub.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.588 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200507/210 X-Sequence-Number: 13451 VACUUM FULL ANALYZE is performed right before tests. UPDATE test SET t = xpath_string(x, 'movie/rating'::text); is performed also to make selects equal. Xpath_string is IMMUTABLE. Table "public.test" Column | Type | Modifiers | Description --------+------------------+-----------+------------- i | integer | | t | text | | x | text | | d | double precision | | Indexes: "floatind" btree (d) "i_i" btree (i) CLUSTER "t_ind" btree (t) "t_x_ind" btree (t, xpath_string(x, 'data'::text)) "x_i" btree (xpath_string(x, 'data'::text)) "x_ii" btree (xpath_string(x, 'movie/characters/character'::text)) "x_iii" btree (xpath_string(x, 'movie/rating'::text)) Has OIDs: no explain analyze select count(*) from ( select * from test order by xpath_string(x, 'movie/rating'::text) limit 1000 offset 10 ) a; QUERY PLAN Aggregate (cost=342.37..342.37 rows=1 width=0) (actual time=403.580..403.584 rows=1 loops=1) -> Subquery Scan a (cost=3.27..339.87 rows=1000 width=0) (actual time=4.252..398.261 rows=1000 loops=1) -> Limit (cost=3.27..329.87 rows=1000 width=969) (actual time=4.242..389.557 rows=1000 loops=1) -> Index Scan using x_iii on test (cost=0.00..3266.00 rows=10000 width=969) (actual time=0.488..381.049 rows=1010 loops=1) Total runtime: 403.695 ms explain analyze select count(*) from ( select * from test order by t limit 1000 offset 10 ) a; QUERY PLAN Aggregate (cost=339.84..339.84 rows=1 width=0) (actual time=26.662..26.666 rows=1 loops=1) -> Subquery Scan a (cost=3.24..337.34 rows=1000 width=0) (actual time=0.228..22.416 rows=1000 loops=1) -> Limit (cost=3.24..327.34 rows=1000 width=969) (actual time=0.217..14.244 rows=1000 loops=1) -> Index Scan using t_ind on test (cost=0.00..3241.00 rows=10000 width=969) (actual time=0.099..6.371 rows=1010 loops=1) Total runtime: 26.749 ms