pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gaetano Mendola <mendola@bigfoot.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: using an index worst performances
Date: Fri, 20 Aug 2004 21:43:24 +0200
Message-ID: <4126545C.5020203@bigfoot.com> (raw)
In-Reply-To: <22026.1093018762@sss.pgh.pa.us>
References: <cg2nrf$sc1$1@floppy.pyrenet.fr>
	<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>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
| Gaetano Mendola <mendola@bigfoot.com> 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-----




view thread (11+ messages)  latest in thread

Message-ID: <4126545C.5020203@bigfoot.com>
Permalink:  ../4126545C.5020203@bigfoot.com/
Also on:    postgresql.org/message-id/4126545C.5020203@bigfoot.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mendola@bigfoot.com, tgl@sss.pgh.pa.us
  Subject: Re: using an index worst performances
  In-Reply-To: <4126545C.5020203@bigfoot.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox