Re: optimal 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 /*