[SQL] optimal sql

2003-01-22 Thread Michael Hostbaek 
Hi,

I am running postgresql 7.2.3 on a test server (with potential of
becoming my production server).

On the server I have a perl script, that is grabbing some data from a
inventory database (local) - with some subselects.
The query is like this:


my $sth = $ppdb->prepare("
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 ? limit 1) 
as partno_main, (SELECT subcat FROM partno_lookup where
partno_lookup.partno_alias ilike 
(?|| inventory.partno ||?) and mfg ilike ? limit 1) as subcat, 
(SELECT key_search FROM partno_lookup where
partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and 
 mfg ilike ? limit 1) as key_search,
(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 ? limit 1) 
 limit 1) as descri from inventory where mfg ilike ? and ? <
create_date $refurbed order by key_search,
subcat, partno_main, status DESC ");


It takes quite a while for the query to get processed - and the script
to return my values.
The inventory table has approx. 23000 records - and the partno_lookup
has approx. 1100.

Is there anyway I can optimize the sql - og perhaps optimize my
postgresql db settings ? ( I am running my postgresql on FreeBSD, on a
fairly adequite machine with 1GB RAM)

I look forward to any pointers or hints you might have.

Thanks.

/mich

-- 
Best Regards,
Michael Landin Hostbaek 
FreeBSDCluster.org - an International Community

*/ PGP-key available upon request /*

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] optimal sql

2003-01-22 Thread Michael Hostbaek 
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:


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 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;


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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] subselect return in where clause

2003-01-28 Thread Michael Hostbaek 
Group,

Is it possible to use a subselect result in a where statement ? If so,
how ?

Fx.
select some_stuff, more_stuff, 
(select other_stuff from other_table where other_table.stuff ilike
stuff.match) as other_stuff from stuff where other_stuff = 'hello'

thanks.
-- 
Best Regards,
Michael Landin Hostbaek 
FreeBSDCluster.org - an International Community

*/ PGP-key available upon request /*

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org