From: Dan Langille <dan@langille.org>
To: pgsql-sql@postgresql.org
Subject: Re: subselect instead of a view...
Date: Tue, 26 Nov 2002 00:07:29 -0500
Message-ID: <3DE2BB41.18930.D5AA1552@localhost> (raw)
In-Reply-To: <3DE2A10D.5202.D543B95A@localhost>
On 25 Nov 2002 at 22:15, Dan Langille wrote:
> I know this can be done better, I just can't figure out how. I keep
> thinking of a subselect but I'm totally blocked. It must be bed time.
It's odd what reading the paper, relaxing with a book, and then
trying to sleep can generate. There I was, almost dropping off, when
I realised I needed this:
SELECT E.name, EP.pathname, E.id, EP2.pathname, CL.id
FROM element E,
element_pathnames EP,
watch_list_element WLE,
element_pathnames EP2,
element E2,
commit_log_elements CLE,
commit_log CL
WHERE WLE.watch_list_id = 3724
and WLE.element_id = E.id
and E.id = EP.id
and EP2.pathname like EP.pathname || '/%'
AND EP2.id = E2.id
AND E2.id = CLE.element_id
AND CLE.commit_log_id = CL.id;
I am still suspicous of that like. It seems to be the performance
killer here. There is an index which can be used:
# explain select * from element_pathnames WHERE pathname like 'abc%';
NOTICE: QUERY PLAN:
Index Scan using element_pathnames_pathname on element_pathnames
(cost=0.00..5.80 rows=1 width=41)
But in the main query, it doesn't get picked up. The explain appears
below (and at http://www.freshports.org/tmp/explain.txt which will be
easier to read than this text-wrapped version). There are quite a
few sequential scans there. I'm confused as to why the indexes are
not being used. A "vacuum analyze" has been run.
Thanks.
Hash Join (cost=266574.28..279596.82 rows=67012 width=118)
-> Hash Join (cost=263685.03..272372.74 rows=67012 width=114)
-> Seq Scan on commit_log_elements cle (cost=0.00..3936.75
rows=216575 width=8)
-> Hash (cost=262995.54..262995.54 rows=36997 width=106)
-> Hash Join (cost=2994.62..262995.54 rows=36997
width=106)
-> Nested Loop (cost=0.00..257416.50 rows=36997
width=102)
-> Nested Loop (cost=0.00..647.08 rows=62
width=61)
-> Nested Loop (cost=0.00..437.06
rows=62 width=20)
-> Index Scan using
watch_list_element_pkey on watch_list_element wle (cost=0.00..229.64
rows=62 width=4)
-> Index Scan using
element_pkey on element e (cost=0.00..3.34 rows=1 width=16)
-> Index Scan using
element_pathnames_pkey on element_pathnames ep (cost=0.00..3.38
rows=1 width=41)
-> Seq Scan on element_pathnames ep2
(cost=0.00..2355.70 rows=119570 width=41)
-> Hash (cost=2286.70..2286.70 rows=119570
width=4)
-> Seq Scan on element e2
(cost=0.00..2286.70 rows=119570 width=4)
-> Hash (cost=2543.20..2543.20 rows=58420 width=4)
-> Seq Scan on commit_log cl (cost=0.00..2543.20 rows=58420
width=4)
--
Dan Langille : http://www.langille.org/
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-sql@postgresql.org
Cc: dan@langille.org
Subject: Re: subselect instead of a view...
In-Reply-To: <3DE2BB41.18930.D5AA1552@localhost>
* 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