Re: optimal sql - Mailing list pgsql-sql

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