pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcan I move sort to first outer join ?
2+ messages / 2 participants
[nested] [flat]
* can I move sort to first outer join ?
@ 2008-05-14 04:40 fernando castano <Fernando.Castano@Sun.COM>
2008-05-14 09:58 ` Re: can I move sort to first outer join ? PFC <lists@peufeu.com>
0 siblings, 1 reply; 2+ messages in thread
From: fernando castano @ 2008-05-14 04:40 UTC (permalink / raw)
To: pgsql-performance
Hi all,
This sql is taking too long for the size of my tiny db. Any tips from
this alias? I tried moving the sort to the first left outer join
(between projects and features tables) using a nested subquery, but
postgres tells me only one column could be returned from a subqueyr.
TIA,
fdo
SELECT projects."id" AS t0_r0, projects."name" AS t0_r1, projects."display_name"
AS t0_r2, projects."description" AS t0_r3, projects."community_id" AS t0_r4, projects."parent_id" AS t0_r5,
projects."visible" AS t0_r6, projects."created_at" AS t0_r7, projects."updated_at" AS t0_r8, projects."image_path"
AS t0_r9, projects."with_navigation" AS t0_r10, projects."static_home" AS t0_r11, projects."active" AS t0_r12,
projects."image_id" AS t0_r13, projects."request_message" AS t0_r14, projects."response_message" AS t0_r15,
projects."approval_status" AS t0_r16, projects."approved_by_id" AS t0_r17, projects."owner_id" AS t0_r18,
project_tags."id" AS t1_r0, project_tags."project_id" AS t1_r1, project_tags."name" AS t1_r2,
project_tags."created_at" AS t1_r3, project_tags."updated_at" AS t1_r4, person_roles."id" AS t2_r0,
person_roles."project_id" AS t2_r1, person_roles."person_id" AS t2_r2, person_roles."role_id" AS t2_r3,
person_roles."authorized" AS t2_r4, person_roles."created_at" AS t2_r5, person_roles."updated_at" AS t2_r6,
person_roles."request_message" AS t2_r7, person_roles."response_message" AS t2_r8, features."id" AS t3_r0,
features."project_id" AS t3_r1, features."name" AS t3_r2, features."display_name" AS t3_r3,
features."feature_uri" AS t3_r4, features."provisioned" AS t3_r5, features."service_name" AS t3_r6,
features."created_at" AS t3_r7, features."updated_at" AS t3_r8, features."active" AS t3_r9,
features."description" AS t3_r10, features."type" AS t3_r11, features."forum_topic_count" AS t3_r12,
features."forum_post_count" AS t3_r13, features."forum_last_post_at" AS t3_r14,
features."forum_last_post_by_id" AS t3_r15, features."wiki_default_page_id" AS t3_r16,
features."wiki_default_page_name" AS t3_r17, features."wiki_format" AS t3_r18,
features."service_id" AS t3_r19, features."service_type_id" AS t3_r20 FROM projects
LEFT OUTER JOIN project_tags ON project_tags.project_id = projects.id
LEFT OUTER JOIN person_roles ON person_roles.project_id = projects.id
LEFT OUTER JOIN features ON features.project_id = projects.id
WHERE (projects."visible" = 't') AND projects.id IN (3, 4, 5, 6, 10, 7, 8, 9, 13, 11)
ORDER BY projects.name asc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=57.17..57.20 rows=12 width=4925) (actual time=147.880..148.325 rows=846 loops=1)
Sort Key: projects.name
-> Hash Left Join (cost=45.53..56.95 rows=12 width=4925) (actual time=1.374..6.694 rows=846 loops=1)
Hash Cond: (projects.id = project_tags.project_id)
-> Hash Left Join (cost=22.48..33.48 rows=4 width=4819) (actual time=1.243..3.018 rows=222 loops=1)
Hash Cond: (projects.id = person_roles.project_id)
-> Hash Left Join (cost=10.90..21.86 rows=4 width=3754) (actual time=1.121..1.702 rows=78 loops=1)
Hash Cond: (projects.id = features.project_id)
-> Seq Scan on projects (cost=0.00..10.90 rows=4 width=1884) (actual time=0.039..0.109 rows=10 loops=1)
Filter: (visible AND (id = ANY ('{3,4,5,6,10,7,8,9,13,11}'::integer[])))
-> Hash (cost=10.40..10.40 rows=40 width=1870) (actual time=1.048..1.048 rows=101 loops=1)
-> Seq Scan on features (cost=0.00..10.40 rows=40 width=1870) (actual time=0.026..0.464 rows=101 loops=1)
-> Hash (cost=10.70..10.70 rows=70 width=1065) (actual time=0.098..0.098 rows=29 loops=1)
-> Seq Scan on person_roles (cost=0.00..10.70 rows=70 width=1065) (actual time=0.014..0.037 rows=29 loops=1)
-> Hash (cost=15.80..15.80 rows=580 width=106) (actual time=0.105..0.105 rows=32 loops=1)
-> Seq Scan on project_tags (cost=0.00..15.80 rows=580 width=106) (actual time=0.013..0.036 rows=32 loops=1)
Total runtime: 149.622 ms
(17 rows)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: can I move sort to first outer join ?
2008-05-14 04:40 can I move sort to first outer join ? fernando castano <Fernando.Castano@Sun.COM>
@ 2008-05-14 09:58 ` PFC <lists@peufeu.com>
0 siblings, 0 replies; 2+ messages in thread
From: PFC @ 2008-05-14 09:58 UTC (permalink / raw)
To: Fernando.Castano@sun.com; pgsql-performance
On Wed, 14 May 2008 06:40:40 +0200, fernando castano
<Fernando.Castano@Sun.COM> wrote:
>
> Hi all,
>
> This sql is taking too long for the size of my tiny db. Any tips from
> this alias? I tried moving the sort to the first left outer join
> (between projects and features tables) using a nested subquery, but
> postgres tells me only one column could be returned from a subqueyr.
Instead of :
SELECT * FROM a LEFT JOIN b LEFT JOIN c WHERE c.column=... ORDER BY c.x
LIMIT N
You could write :
SELECT * FROM a LEFT JOIN b LEFT JOIN (SELECT * FROM c WHERE c.column=...
ORDER BY c.x LIMIT N) AS cc ORDER BY cc.x LIMIT N
This is only interesting of you use a LIMIT and this allows you to reduce
the number of rows sorted/joined.
However in your case this is not the right thing to do since you do not
use LIMIT, and sorting your 846 rows will only take a very small time.
Your problem are those seq scans, you need to optimize that query so it
can use indexes.
> -> Seq Scan on projects (cost=0.00..10.90 rows=4
> width=1884) (actual time=0.039..0.109 rows=10 loops=1)
> Filter: (visible AND (id = ANY
> ('{3,4,5,6,10,7,8,9,13,11}'::integer[])))
> -> Hash (cost=10.40..10.40 rows=40 width=1870)
> (actual time=1.048..1.048 rows=101 loops=1)
> -> Seq Scan on features (cost=0.00..10.40
> rows=40 width=1870) (actual time=0.026..0.464 rows=101 loops=1)
> -> Hash (cost=10.70..10.70 rows=70 width=1065) (actual
> time=0.098..0.098 rows=29 loops=1)
> -> Seq Scan on person_roles (cost=0.00..10.70
> rows=70 width=1065) (actual time=0.014..0.037 rows=29 loops=1)
> -> Hash (cost=15.80..15.80 rows=580 width=106) (actual
> time=0.105..0.105 rows=32 loops=1)
> -> Seq Scan on project_tags (cost=0.00..15.80 rows=580
> width=106) (actual time=0.013..0.036 rows=32 loops=1)
> Total runtime: 149.622 ms
All those seq scans !!!
Please post, for each of those tables :
- The total number of rows (SELECT count(*) is fine)
- The table definitions with indexes (\d table)
EXPLAIN ANALYZE tells you the number of rows it picked out of a seq scan
(that's the "rows=") but not the number of rows scanned... this is
important, because a seq scan on a small table isn't a problem, but on a
big one, it is.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-05-14 09:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-14 04:40 can I move sort to first outer join ? fernando castano <Fernando.Castano@Sun.COM>
2008-05-14 09:58 ` PFC <lists@peufeu.com>
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