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 383345E46C3 for ; Fri, 20 Aug 2004 16:43:44 -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 59941-06 for ; Fri, 20 Aug 2004 19:43:41 +0000 (GMT) Received: from pns.mm.eutelsat.org (pns.mm.eutelsat.org [194.214.173.227]) by svr1.postgresql.org (Postfix) with ESMTP id 227A45E46C8 for ; Fri, 20 Aug 2004 16:43:39 -0300 (ADT) Received: from nts-03.mm.eutelsat.org (localhost [127.0.0.1]) by pns.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id i7KJgu220915; Fri, 20 Aug 2004 21:42:56 +0200 Received: from [127.0.0.1] (accesspoint.mm.eutelsat.org [194.214.173.4]) by nts-03.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id i7KJhck02148; Fri, 20 Aug 2004 21:43:38 +0200 Message-ID: <4126545C.5020203@bigfoot.com> Date: Fri, 20 Aug 2004 21:43:24 +0200 From: Gaetano Mendola User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane , "pgsql-performance@postgresql.org" Subject: Re: using an index worst performances References: <4124ECF3.6040901@archonet.com> <412505F6.6080000@bigfoot.com> <4125565D.3060008@familyhealth.com.au> <4125C657.7000702@bigfoot.com> <1093006549.75942.61.camel@jester> <41261E41.3080204@bigfoot.com> <22026.1093018762@sss.pgh.pa.us> In-Reply-To: <22026.1093018762@sss.pgh.pa.us> X-Enigmail-Version: 0.85.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/308 X-Sequence-Number: 7944 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Tom Lane wrote: | Gaetano Mendola writes: | |>Using a prepared query: | | |>Without index and default stat 10 : 1.12 ms ariadne=# explain analyze execute test_ariadne; ~ QUERY PLAN - ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ~ Limit (cost=46.15..46.17 rows=1 width=760) (actual time=0.926..1.035 rows=3 loops=1) ~ -> Unique (cost=46.15..46.17 rows=1 width=760) (actual time=0.904..0.969 rows=3 loops=1) ~ -> Sort (cost=46.15..46.15 rows=1 width=760) (actual time=0.891..0.909 rows=3 loops=1) ~ Sort Key: store_nodes.parent, store_nodes.priority, store_nodes."path", store_objects.id, store_objects."type", store_objects.object, date_part('epoch'::text, store_objects.lastchanged), store_objects.vtype ~ -> Hash Join (cost=1.74..46.14 rows=1 width=760) (actual time=0.342..0.825 rows=3 loops=1) ~ Hash Cond: (("outer".vtype)::text = ("inner"."type")::text) ~ -> Nested Loop (cost=0.00..44.38 rows=1 width=760) (actual time=0.198..0.618 rows=3 loops=1) ~ -> Nested Loop (cost=0.00..38.93 rows=1 width=104) (actual time=0.157..0.447 rows=3 loops=1) ~ -> Seq Scan on store_prop_article (cost=0.00..1.75 rows=7 width=8) (actual time=0.030..0.119 rows=7 loops=1) ~ Filter: ((ar_start <= 1092924200) AND (ar_end >= 1092924200) AND ((ar_display)::text = 'default'::text)) ~ -> Index Scan using store_nodes_object on store_nodes (cost=0.00..5.30 rows=1 width=96) (actual time=0.019..0.023 rows=0 loops=7) ~ Index Cond: ("outer".object = store_nodes.object) ~ Filter: (("path")::text ~~ '/sites/broadsat/news/%'::text) ~ -> Index Scan using store_objects_pkey on store_objects (cost=0.00..5.43 rows=1 width=672) (actual time=0.013..0.020 rows=1 loops=3) ~ Index Cond: ("outer".object = store_objects.id) ~ -> Hash (cost=1.74..1.74 rows=2 width=11) (actual time=0.085..0.085 rows=0 loops=1) ~ -> Seq Scan on store_types (cost=0.00..1.74 rows=2 width=11) (actual time=0.038..0.064 rows=1 loops=1) ~ Filter: ((implements)::text = 'particle'::text) ~ Total runtime: 1.199 ms (19 rows) |>Without index and default stat 1000 : 1.25 ms ariadne=# explain analyze execute test_ariadne; ~ QUERY PLAN - ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ~ Limit (cost=46.14..46.16 rows=1 width=760) (actual time=1.027..1.126 rows=3 loops=1) ~ -> Unique (cost=46.14..46.16 rows=1 width=760) (actual time=1.014..1.077 rows=3 loops=1) ~ -> Sort (cost=46.14..46.14 rows=1 width=760) (actual time=1.001..1.019 rows=3 loops=1) ~ Sort Key: store_nodes.parent, store_nodes.priority, store_nodes."path", store_objects.id, store_objects."type", store_objects.object, date_part('epoch'::text, store_objects.lastchanged), store_objects.vtype ~ -> Nested Loop (cost=0.00..46.13 rows=1 width=760) (actual time=0.278..0.933 rows=3 loops=1) ~ Join Filter: (("outer".vtype)::text = ("inner"."type")::text) ~ -> Nested Loop (cost=0.00..44.38 rows=1 width=760) (actual time=0.208..0.591 rows=3 loops=1) ~ -> Nested Loop (cost=0.00..38.93 rows=1 width=104) (actual time=0.168..0.417 rows=3 loops=1) ~ -> Seq Scan on store_prop_article (cost=0.00..1.75 rows=7 width=8) (actual time=0.038..0.118 rows=7 loops=1) ~ Filter: ((ar_start <= 1092924200) AND (ar_end >= 1092924200) AND ((ar_display)::text = 'default'::text)) ~ -> Index Scan using store_nodes_object on store_nodes (cost=0.00..5.30 rows=1 width=96) (actual time=0.016..0.020 rows=0 loops=7) ~ Index Cond: ("outer".object = store_nodes.object) ~ Filter: (("path")::text ~~ '/sites/broadsat/news/%'::text) ~ -> Index Scan using store_objects_pkey on store_objects (cost=0.00..5.43 rows=1 width=672) (actual time=0.012..0.022 rows=1 loops=3) ~ Index Cond: ("outer".object = store_objects.id) ~ -> Seq Scan on store_types (cost=0.00..1.74 rows=1 width=11) (actual time=0.029..0.060 rows=1 loops=3) ~ Filter: ((implements)::text = 'particle'::text) ~ Total runtime: 1.288 ms (18 rows) |>With index and default stat 10: 1.35 ms ariadne=# explain analyze execute test_ariadne; ~ QUERY PLAN - ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ~ Limit (cost=14.95..14.97 rows=1 width=760) (actual time=1.066..1.165 rows=3 loops=1) ~ -> Unique (cost=14.95..14.97 rows=1 width=760) (actual time=1.052..1.116 rows=3 loops=1) ~ -> Sort (cost=14.95..14.95 rows=1 width=760) (actual time=1.036..1.054 rows=3 loops=1) ~ Sort Key: store_nodes.parent, store_nodes.priority, store_nodes."path", store_objects.id, store_objects."type", store_objects.object, date_part('epoch'::text, store_objects.lastchanged), store_objects.vtype ~ -> Hash Join (cost=3.51..14.94 rows=1 width=760) (actual time=0.614..0.968 rows=3 loops=1) ~ Hash Cond: ("outer".id = "inner".object) ~ -> Hash Join (cost=1.74..13.15 rows=1 width=768) (actual time=0.281..0.651 rows=5 loops=1) ~ Hash Cond: (("outer".vtype)::text = ("inner"."type")::text) ~ -> Nested Loop (cost=0.00..11.39 rows=1 width=768) (actual time=0.070..0.406 rows=6 loops=1) ~ -> Index Scan using test_index on store_nodes (cost=0.00..5.95 rows=1 width=96) (actual time=0.027..0.084 rows=6 loops=1) ~ Index Cond: ((("path")::text ~>=~ '/sites/broadsat/news/'::character varying) AND (("path")::text ~<~ '/sites/broadsat/news0'::character varying)) ~ Filter: (("path")::text ~~ '/sites/broadsat/news/%'::text) ~ -> Index Scan using store_objects_pkey on store_objects (cost=0.00..5.43 rows=1 width=672) (actual time=0.012..0.020 rows=1 loops=6) ~ Index Cond: (store_objects.id = "outer".object) ~ -> Hash (cost=1.74..1.74 rows=2 width=11) (actual time=0.093..0.093 rows=0 loops=1) ~ -> Seq Scan on store_types (cost=0.00..1.74 rows=2 width=11) (actual time=0.029..0.054 rows=1 loops=1) ~ Filter: ((implements)::text = 'particle'::text) ~ -> Hash (cost=1.75..1.75 rows=7 width=8) (actual time=0.182..0.182 rows=0 loops=1) ~ -> Seq Scan on store_prop_article (cost=0.00..1.75 rows=7 width=8) (actual time=0.041..0.121 rows=7 loops=1) ~ Filter: ((ar_start <= 1092924200) AND (ar_end >= 1092924200) AND ((ar_display)::text = 'default'::text)) ~ Total runtime: 1.358 ms (21 rows) |>With index and default stat 1000: 1.6 ms ariadne=# explain analyze execute test_ariadne; ~ QUERY PLAN - ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ~ Limit (cost=14.94..14.96 rows=1 width=760) (actual time=1.346..1.445 rows=3 loops=1) ~ -> Unique (cost=14.94..14.96 rows=1 width=760) (actual time=1.329..1.393 rows=3 loops=1) ~ -> Sort (cost=14.94..14.94 rows=1 width=760) (actual time=1.317..1.335 rows=3 loops=1) ~ Sort Key: store_nodes.parent, store_nodes.priority, store_nodes."path", store_objects.id, store_objects."type", store_objects.object, date_part('epoch'::text, store_objects.lastchanged), store_objects.vtype ~ -> Hash Join (cost=1.77..14.93 rows=1 width=760) (actual time=0.663..1.249 rows=3 loops=1) ~ Hash Cond: ("outer".id = "inner".object) ~ -> Nested Loop (cost=0.00..13.14 rows=1 width=768) (actual time=0.268..0.936 rows=5 loops=1) ~ Join Filter: (("outer".vtype)::text = ("inner"."type")::text) ~ -> Nested Loop (cost=0.00..11.39 rows=1 width=768) (actual time=0.070..0.412 rows=6 loops=1) ~ -> Index Scan using test_index on store_nodes (cost=0.00..5.95 rows=1 width=96) (actual time=0.027..0.093 rows=6 loops=1) ~ Index Cond: ((("path")::text ~>=~ '/sites/broadsat/news/'::character varying) AND (("path")::text ~<~ '/sites/broadsat/news0'::character varying)) ~ Filter: (("path")::text ~~ '/sites/broadsat/news/%'::text) ~ -> Index Scan using store_objects_pkey on store_objects (cost=0.00..5.43 rows=1 width=672) (actual time=0.013..0.020 rows=1 loops=6) ~ Index Cond: (store_objects.id = "outer".object) ~ -> Seq Scan on store_types (cost=0.00..1.74 rows=1 width=11) (actual time=0.025..0.051 rows=1 loops=6) ~ Filter: ((implements)::text = 'particle'::text) ~ -> Hash (cost=1.75..1.75 rows=7 width=8) (actual time=0.181..0.181 rows=0 loops=1) ~ -> Seq Scan on store_prop_article (cost=0.00..1.75 rows=7 width=8) (actual time=0.040..0.122 rows=7 loops=1) ~ Filter: ((ar_start <= 1092924200) AND (ar_end >= 1092924200) AND ((ar_display)::text = 'default'::text)) ~ Total runtime: 1.616 ms (20 rows) | Could we see EXPLAIN ANALYZE EXECUTE output for each case? | See above. BTW I dont know if this is a known issue: After the prepare statement: ariadne=# drop index test_index; DROP INDEX ariadne=# explain analyze execute test_ariadne; ERROR: could not open relation with OID 53695 Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBJlRb7UpzwH2SGd4RAn+/AJ9QEyedv6ZQNQse5uhhCpasF65dugCfUzW7 tDuDEVFNgb42NbX2/GJ+joQ= =gaO/ -----END PGP SIGNATURE-----