pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Michael Hostbaek <mich@the-lab.org>
To: pgsql-sql@postgresql.org
Subject: Re: optimal sql
Date: Wed, 22 Jan 2003 11:14:28 +0100
Message-ID: <20030122101428.GA55229@mich2.itxmarket.com> (raw )
In-Reply-To: <3E2E6C2F.6020902@klaster.net >
References: <20030122085454.GA55124@mich2.itxmarket.com >
<3E2E6C2F.6020902@klaster.net >
Tomasz Myrta (jasiek) writes:
>
> 3. Explain analyze would be helpful like in most performance cases...
> The same with SQL query instead of Perl script.
Explain analyze:
<explain>
NOTICE: QUERY PLAN:
Limit (cost=27.55..27.55 rows=1 width=183) (actual
time=35364.89..35365.04 rows=10 loops=1)
-> Sort (cost=27.55..27.55 rows=1 width=183) (actual
time=35364.87..35364.92 rows=11 loops=1)
-> Group (cost=27.51..27.54 rows=1 width=183) (actual
time=35350.49..35359.96 rows=411 loops=1)
-> Sort (cost=27.51..27.51 rows=1 width=183) (actual
time=35350.43..35352.52 rows=411 loops=1)
-> Seq Scan on inventory (cost=0.00..27.50 rows=1
width=183) (actual time=168.52..35342.92 rows=411 loops=1)
SubPlan
-> Limit (cost=0.00..30.00 rows=1
width=48) (actual time=4.99..6.14 rows=0 loops=411)
-> Seq Scan on partno_lookup
(cost=0.00..30.00 rows=1 width=48) (actual time=4.96..6.11 rows=1
loops=411)
-> Limit (cost=0.00..30.00 rows=1
width=93) (actual time=4.97..6.13 rows=0 loops=411)
-> Seq Scan on partno_lookup
(cost=0.00..30.00 rows=1 width=93) (actual time=4.95..6.10 rows=1
loops=411)
-> Limit (cost=0.00..4.50 rows=1 width=32)
(actual time=57.94..73.46 rows=0 loops=411)
InitPlan
-> Limit (cost=0.00..30.00 rows=1
width=48) (actual time=5.00..6.16 rows=0 loops=411)
-> Seq Scan on partno_lookup
(cost=0.00..30.00 rows=1 width=48) (actual time=4.98..6.13 rows=1
loops=411)
-> Seq Scan on descriptions
(cost=0.00..22.50 rows=5 width=32) (actual time=57.91..73.43 rows=1
loops=411)
Total runtime: 35365.50 msec
EXPLAIN
</explain>
<real query>
explain analyze select partno, create_date, mfg, condition, gescode,
qty, cmup,(SELECT partno_main FROM
partno_lookup where partno_lookup.partno_alias ilike
'%'||inventory.partno||'%' and mfg ilike 'CISCO' limit 1)
as partno_main, (SELECT subcat FROM partno_lookup where
partno_lookup.partno_alias ilike
'%'||inventory.partno||'%' and mfg ilike 'CISCO' limit 1) as subcat,
(SELECT text_desc FROM descriptions where descriptions.partno=(SELECT
partno_main FROM partno_lookup
where partno_lookup.partno_alias ilike '%'||inventory.partno||'%' and
mfg ilike 'CISCO' limit 1) limit 1) as
descri from inventory where mfg ilike 'CISCO' and '2003-01-15' <
create_date and condition not like 'REFURB'
group by partno_main, partno, create_date, mfg, condition, gescode, qty,
cmup, subcat, descri, status order by
subcat, partno_main, status DESC limit 10;
</real query>
Here is a sample of how a partno_lookup record looks like:
ppdb=> select * from partno_lookup where partno_main = 'WIC-2T';
partno_main | partno_alias | mfg |
subcat | key_search
-------------+---------------------------------------------+-------+-------------+------------
WIC-2T | WIC2TB,WIC-2T,WIC-2T=,WIC2T,WIC2T=,WIC2TREF | CISCO | WIC
MODULES | A
(1 row)
Any help very much appreciated.
/mich
--
Best Regards,
Michael Landin Hostbaek
FreeBSDCluster.org - an International Community
*/ PGP-key available upon request /*
view thread (7+ messages) latest in thread
Message-ID: <20030122101428.GA55229@mich2.itxmarket.com>
Permalink: ../20030122101428.GA55229@mich2.itxmarket.com/
Also on: postgresql.org/message-id/20030122101428.GA55229@mich2.itxmarket.com
copy link · copy postgr.es
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-sql@postgresql.org
Cc: mich@the-lab.org
Subject: Re: optimal sql
In-Reply-To: <20030122101428.GA55229@mich2.itxmarket.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