From: Tomasz Myrta <jasiek@klaster.net>
To: Michael Hostbaek <mich@the-lab.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: optimal sql
Date: Wed, 22 Jan 2003 11:02:23 +0100
Message-ID: <3E2E6C2F.6020902@klaster.net> (raw)
In-Reply-To: <20030122085454.GA55124@mich2.itxmarket.com>
References: <20030122085454.GA55124@mich2.itxmarket.com>
Michael Hostbaek wrote:
>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 ");
1. Probably your query can't use index on table partno_lookup.partno_alias.
Consider creating table aliases which contains all possible parts aliases.
You can change then "ilike" into "=" which will use indexes.
2. You don't need subselects in your query. You can change them into ordinary
table joins and use "group by" or "distinct on". In your case selecting from
partno_lookup is executed several times per one row.
3. Explain analyze would be helpful like in most performance cases...
The same with SQL query instead of Perl script.
4. This is rather a sql problem, than hardware/configuration one.
Regards,
Tomasz Myrta
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, mich@the-lab.org
Subject: Re: optimal sql
In-Reply-To: <3E2E6C2F.6020902@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox