[ic] slow one click search

[Christian Brink]

> Hi,
> IC 4.9.7
> PostgreSQL 7.2.3

> select t1.* from products t1, merchandising t2 where t1.sku=t2.sku and
> t1.category like '%[sql-param code]%' and t2.featured like '%second%'
> and t1.inactive='0' LIMIT 4

Have you done an 'EXPLAIN' on the sql query? I know you said you ran it
against the DB but getting the execution plan and cost can be really
helpful. The next step is to see what is exactly is getting sent to the db
and how it relates to what you think is going on. Interchange will
sometimes do a 'SELECT * FROM products' and do the search criteria in
code.

So turn on postgresql's debugging and get a log file.
in your postgresql.conf:

debug_level = 1 # range 0-16
debug_print_query = true
#debug_print_parse = false
#debug_print_rewritten = false
debug_print_plan = true
debug_pretty_print = true

then start your postmaster like:

/usr/bin/pg_ctl  -D $PGDATA -p /usr/bin/postmaster start  >>
/var/log/pgsql 2>&1" < /dev/null

You'll get a logfile of all the query's that are executed. Check what's
really being executed.

You'll want to turn debugging off when you're done it will generate a huge
log file.

If you need help reading the log file email me and I can send you my perl
script I use to get a sql performance profile from that log file.

HTH
Christian