Well, your expected vs. actual rows are off, so analyzing might help.
Otherwise, what is your sort_mem set to?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Mar 14, 2005, at 6:11 AM, Net Virtual Mailing Lists wrote:
Hello,
I am sorry to bring this up again.... Does anyone have any idea what
might be going on here?... I'm very worried about this situation..
;-(
- Greg
Something even more peculiar (at least it seems to me..)...
If I drop the index table1_category_gist_idx, I get this:
jobs=> explain analyze select id from table1 where category <@ 'a.b'
ORDER BY category;
QUERY PLAN
-------------------------------------
------------------------------------
------------------------------------------
Sort (cost=7568.55..7568.62 rows=28 width=52) (actual
time=4842.691..4854.468 rows=1943 loops=1)
Sort Key: category
-> Seq Scan on jobdata (cost=0.00..7567.88 rows=28 width=52)
(actual
time=11.498..4800.907 rows=1943 loops=1)
Filter: (category <@ 'a.b'::ltree)
Total runtime: 4871.076 ms
(5 rows)
.. no disk thrashing all over the place..
I'm really perplexed about this one..;-(
- Greg
I have a rather peculiar performance observation and would welcome
any
feedback on this.....
First off, the main table (well, part of it.. it is quite large..):
Table "table1"
Column | Type |
Modifiers
--------------------+--------------------------
+-----------------------------------------------------------------
id | integer | not null default
nextval('master.id_seq'::text)
user_id | integer |
... (skipping about 20 columns)
category | ltree[] |
somedata | text | not null
Indexes:
"table1_pkey" primary key, btree (id)
"table1_category_full_gist_idx" gist (category)
"table1_id_idx" btree (id)
"table1_fti_idx" gist (fti) WHERE ((status)::text = 'open'::text)
"table1_user_id_idx" btree (user_id)
database=> explain analyze select id from table1 where category <@
'a.b';
QUERY
PLAN
-------------------------------------
-------------------------------------
---------------------------------------------------------------------
----
Index Scan using table1_category_full_gist_idx on jobdata
(cost=0.00..113.48 rows=28 width=4) (actual time=43.814..12201.528
rows=1943 loops=1)
Index Cond: (category <@ 'a.b'::ltree)
Filter: (category <@ 'a.b'::ltree)
Total runtime: 12222.258 ms
If I do this:
create table yuck (id integer, category ltree[]);
insert into yuck select id, category from table1;
create index category_idx on yuck using gist(category);
vacuum analyze yuck;
jobs=> explain analyze select id from table1 where id in (select id
from
yuck where category <@ 'a.b');
QUERY
PLAN
-------------------------------------
-------------------------------------
-------------------------------------------------------------
Nested Loop (cost=108.64..114.28 rows=1 width=52) (actual
time=654.645..1245.212 rows=1943 loops=1)
-> HashAggregate (cost=108.64..108.64 rows=1 width=4) (actual
time=654.202..690.709 rows=1943 loops=1)
-> Index Scan using category_idx on yuck (cost=0.00..108.57
rows=28 width=4) (actual time=2.046..623.436 rows=1943 loops=1)
Index Cond: (category <@ 'a.b'::ltree)
Filter: (category <@ 'a.b'::ltree)
-> Index Scan using table1_pkey on jobdata (cost=0.00..5.64
rows=1
width=52) (actual time=0.219..0.235 rows=1 loops=1943)
Index Cond: (table1.id = "outer".id)
Total runtime: 1261.551 ms
(8 rows)
In the first query, my hard disk trashes audibly the entire 12
seconds
(this is actually the best run I could get, it is usually closer to
20
seconds), the second query runs almost effortlessly.. I've tried
reindexing, even dropping the index and recreating it but nothing I
do
helps at all.
Now keep in mind that I do all of my development on painfully slow
hardware in order to make any performance issues really stand out.
But,
I've done this on production servers too with an equal performance
improvement noticed.
I just can't figure out why this second query is so much faster, I
feel
like I must have done something very wrong in my schema design or
something to be suffering this sort of a performance loss. Any idea
what I can do about this?
Thanks as always!
- Greg
---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
