Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id E9B46475DC5
	for <pgsql-sql@postgresql.org>; Wed, 22 Jan 2003 05:14:27 -0500 (EST)
Received: from mail2.itxmarket.com (fw.itxmarket.com [213.11.40.109])
	by postgresql.org (Postfix) with ESMTP id EDA1D475BC3
	for <pgsql-sql@postgresql.org>; Wed, 22 Jan 2003 05:14:26 -0500 (EST)
Received: from freebsdcluster.net (mich2.itxmarket.com [192.168.2.26])
	by mail2.itxmarket.com (Postfix) with ESMTP id 73B6E37CB2
	for <pgsql-sql@postgresql.org>; Wed, 22 Jan 2003 11:14:28 +0100 (CET)
Received: by freebsdcluster.net (Postfix, from userid 1000)
	id 5C2513B943E; Wed, 22 Jan 2003 11:14:28 +0100 (CET)
Date: Wed, 22 Jan 2003 11:14:28 +0100
From: Michael Hostbaek <mich@the-lab.org>
To: pgsql-sql@postgresql.org
Subject: Re: optimal sql
Message-ID: <20030122101428.GA55229@mich2.itxmarket.com>
Mail-Followup-To: Michael Hostbaek <mich@the-lab.org>,
 pgsql-sql@postgresql.org
References: <20030122085454.GA55124@mich2.itxmarket.com>
	<3E2E6C2F.6020902@klaster.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3E2E6C2F.6020902@klaster.net>
X-Operating-System: FreeBSD 4.7-STABLE i386
X-PGP-Key: http://www.freebsdcluster.org/~mich/
User-Agent: Mutt/1.5.3i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/329
X-Sequence-Number: 11194

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 /*