Michael Hostbaek wrote:
Main problem of your query is this: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:
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
