Re: optimal sql

Michael Hostbaek wrote:

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

Main problem of your query is this:
Seq Scan on inventory (cost=0.00..27.50 rows=1
width=183) (actual time=168.52..35342.92 rows=411 loops=1)

Do you have to use "ilike" condition in all cases?
Database won't use index on this table at all, which
compared to thousands of records isn't good.

Next problem - your table isn't too normalized...

I don't know, how much have you done to your database,
but I think, you should reorganize it.

Example:
Create table manufacturers
( mfgid integer,
name varchar (for example "Cisco")
)
In table inventory change field mfg into mfgid.
In table partno_aliases change field mfg into mfgid.

Your query would have something like this:
select ...
from manufacturers M join inventory I using (mfgid)
join partno_aliases PA using (mfgid)
where M.name ilike 'Cisco' and ...

After this create index on inventory(mfgid,createdate)

If you don't want to change anything,
create at least index on inventory(createdate).
This will speed up queries with recent products - for
not too old createdate.

Regards,
Tomasz Myrta