1

I posted a previous question here asking about what was better, JOIN queries or queries using subselects. Link: Queries within queries: Is there a better way?

This is an extension to that question. Can somebody explain to me why I'm seeing what I'm seeing here?

Query (Subselects):

SELECT article_seq, title, synopsis, body, lastmodified_date, (SELECT type_id FROM types WHERE kbarticles.type = type_seq), status, scope, images, archived, author, owner, (SELECT owner_description FROM owners WHERE kbarticles.owner = owner_seq),  (SELECT review_date FROM kbreview WHERE kbarticles.article_seq = article_seq) FROM kbarticles WHERE article_seq = $1

Explain Analyze (Subselects)

QUERY PLAN

Index Scan using article_seq_pkey on kbarticles  (cost=0.00..32.24 rows=1 width=1241) (actual time=1.421..1.426 rows=1 loops=1)

  Index Cond: (article_seq = 1511)

  SubPlan

    ->  Seq Scan on kbreview  (cost=0.00..14.54 rows=1 width=8) (actual time=0.243..1.158 rows=1 loops=1)

          Filter: ($2 = article_seq)

    ->  Seq Scan on owners  (cost=0.00..1.16 rows=1 width=24) (actual time=0.073..0.078 rows=1 loops=1)

          Filter: ($1 = owner_seq)

    ->  Index Scan using types_type_seq_key on types  (cost=0.00..8.27 rows=1 width=24) (actual time=0.044..0.050 rows=1 loops=1)

          Index Cond: ($0 = type_seq)

Total runtime: 2.051 ms

Query (JOINs)

SELECT k.article_seq, k.title, k.synopsis, k.body, k.lastmodified_date, t.type_id, k.status, k.scope, k.images, k.archived, k.author, k.owner, o.owner_description, r.review_date FROM kbarticles k JOIN types t ON k.type = t.type_seq JOIN owners o ON k.owner = o.owner_seq JOIN kbreview r ON k.article_seq = r.article_seq WHERE k.article_seq = $1

Explain Analyze (JOINs)

QUERY PLAN

Nested Loop  (cost=0.00..32.39 rows=1 width=1293) (actual time=0.532..1.467 rows=1 loops=1)

  Join Filter: (k.owner = o.owner_seq)

  ->  Nested Loop  (cost=0.00..31.10 rows=1 width=1269) (actual time=0.419..1.345 rows=1 loops=1)

        ->  Nested Loop  (cost=0.00..22.82 rows=1 width=1249) (actual time=0.361..1.277 rows=1 loops=1)

              ->  Index Scan using article_seq_pkey on kbarticles k  (cost=0.00..8.27 rows=1 width=1241) (actual time=0.065..0.071 rows=1 loops=1)

                    Index Cond: (article_seq = 1511)

              ->  Seq Scan on kbreview r  (cost=0.00..14.54 rows=1 width=12) (actual time=0.267..1.175 rows=1 loops=1)

                    Filter: (r.article_seq = 1511)

        ->  Index Scan using types_type_seq_key on types t  (cost=0.00..8.27 rows=1 width=28) (actual time=0.048..0.055 rows=1 loops=1)

              Index Cond: (t.type_seq = k.type)

  ->  Seq Scan on owners o  (cost=0.00..1.13 rows=13 width=28) (actual time=0.022..0.038 rows=13 loops=1)

Total runtime: 2.256 ms

Based on the answers given (and accepted) in my previous question, JOINs should prove to have better results. However, in all my tests, I'm seeing JOINs to have worse results by a few milliseconds. It also seems like the JOINs are riddled with nested loops. All the tables I'm JOINing are indexed.

Am I doing something that I should be doing differently? Is there something I'm missing?

Improve this question
1
  • how many rows have those tables ? Subquery can be faster on small amount of row... Have you try on 10.000 rows or more ? Commented Jan 14, 2011 at 15:58

3 Answers 3

Reset to default
2

These queries are logically different.

The first one:

SELECT  article_seq, title, synopsis, body, lastmodified_date,
        (
        SELECT  type_id
        FROM    types
        WHERE   kbarticles.type = type_seq
        ),
        status, scope, images, archived, author, owner,
        (
        SELECT  owner_description
        FROM    owners
        WHERE   kbarticles.owner = owner_seq
        ),
        (
        SELECT  review_date
        FROM    kbreview
        WHERE   kbarticles.article_seq = article_seq
        )
FROM    kbarticles
WHERE   article_seq = $1

The second one:

SELECT  k.article_seq, k.title, k.synopsis, k.body, k.lastmodified_date, t.type_id, k.status,
        k.scope, k.images, k.archived, k.author, k.owner, o.owner_description, r.review_date
FROM    kbarticles k
JOIN    types t
ON      k.type = t.type_seq
JOIN    owners o
ON      k.owner = o.owner_seq
JOIN    kbreview r
ON      k.article_seq = r.article_seq
WHERE   k.article_seq = $1

If there is more than one record in types, owners or kbreview, the first query will fail while the second one will return duplicates from kbarticles.

If there is no types, owners or kbreviews for a kbarticle, the first query will return a NULL in appropriate field, while the second one will just omit that record.

If the *_seq fields seem to be the PRIMARY KEY fields, there will never be duplicates and the query will never fail; in the same way if kbarticles is constrained with FOREIGN KEY references to types, owners or kbreview, there can be no missing rows.

However, JOIN operators give the optimizer more place: it can make any table leading and use more advanced JOIN techniques like HASH JOIN or MERGE JOIN which are not available if you are using subqueries.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

So, basically you're saying I should probably stick with JOINs as they provide more benefits in the long run and really make a few milliseconds negligible?
@Michael: as I said, these queries are logically different. They are only same if both conditions I mention hold: *_seq fields are PRIMARY KEY and you have FOREIGN KEY constraints on kbarticles referencing all of them. If they do not (and they most probably do not, since kbreview.article_seq does not seem to be a PRIMARY KEY), then you should choose which of them you want. Can you have more then one review per article? If you can, the first query will fail for articles having more than one review.
kbreview.article_seq is not a primary key for sure. There is another column with a unique primary key to the kbreview table. However, kbreview.article_seq is definitely unique and should never contain duplicate values. I noticed that the index I created on that column did not have the unique constraint on it and so I've fixed that now.
@Michael: can an article have more than one or zero type, owner or kbreview records?
@Michael: then your queries are identical, but it is better to use JOIN anyway.
|
0

Is this table column indexed? r.article_seq

-> Seq Scan on kbreview r (cost=0.00..14.54 rows=1 width=12) (actual time=0.267..1.175 rows=1 loops=1)

This is where most time is spend.

Improve this answer

1 Comment

Yes, there is an index for that column. kbreview_article_seq CREATE INDEX kbreview_article_seq ON kbreview USING btree (article_seq)
0

Given that both plans are doing the same table scans, just arranged in a different way, I'd say there's no significant difference between the two. A "nested loop" where the lower arm produces a single row is pretty much the same as a single-row subselect.

Joins are more general, since using scalar subselects won't extend to getting two columns from any of those auxiliary tables, for example.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.