From: Ümit Öztosun <umit@likyabilisim.com>
To: pgsql-performance@postgresql.org
Subject: Slow queries consisting inner selects and order bys & hack to speed up
Date: Sat, 21 Jan 2006 22:55:23 +0200
Message-ID: <d95439ec0601211255y4ac7ad91yeb7c9beb76e3e57c@mail.gmail.com> (raw)
Hello,
Our application uses typical queries similar to following (very simplified):
SELECT
part_id,
part_name,
(SELECT
SUM(amount) FROM part_movements M
WHERE P.part_id = M.part_id
) as part_amount
FROM parts P
LIMIT 50
The parts table holds thousands of items. Movement table stores yearly
movement information of those items. We are presenting results to users page
by page, hence the limit case.
User can sort and filter results. When sorting is introduced, query
performance drops significantly:
SELECT
part_id,
part_name,
(SELECT
SUM(amount) FROM part_movements M
WHERE P.part_id = M.part_id
) as part_amount
FROM parts P
ORDER BY part_name
LIMIT 50
Postgres seems to compute all possible rows and then sorts the
results, which nearly renders the paging meaningless. A dummy WHERE
case dramatically improves performance:
SELECT
part_id,
part_name,
(SELECT
SUM(amount) FROM part_movements M
WHERE P.part_id = M.part_id
) as part_amount
FROM parts P
ORDER BY part_name
WHERE part_amount > -10000000
LIMIT 50
Is there a way to improve performance of these queries? Is it possible
to instruct Postgres to first sort the rows then compute the inner
queries? (We have simulated this by using temporary tables and two
stage queries, but this is not practical because most queries are
automatically generated).
Attached is the output of real queries and their corresponding EXPLAIN
ANALYZE outputs.
Regards,
Umit Oztosun
Attachments:
pgperf.zip (application/zip, 6.3K) download
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-performance@postgresql.org
Cc: umit@likyabilisim.com
Subject: Re: Slow queries consisting inner selects and order bys & hack to speed up
In-Reply-To: <d95439ec0601211255y4ac7ad91yeb7c9beb76e3e57c@mail.gmail.com>
* 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