Re: Query Performance

On 2022-09-17 05:28:25 +0000, sivapostgres(at)yahoo(dot)com wrote:
> My query is like this
>
> Select a.field1, a.field2, a.field3
> From (Select a.field1, b.field2, c.field3
> From table1 a
> Join table2 b
> on b.something = a.something
> Join table3 c
> On c.something = a.something
> Where a.field7 = 'value'
>
> UNION ALL
>
> Select a.field4, a.field5, a.field6
> From table11 a
> Join table21 b
> On b.something = a.something
> Where a.field8 = 'something' ) a
> Join table10 b
> On b.field11 = (Select c.field11
> From table10 c
> Where c.field10 = a.field1 ) <-
> instead of a.field1, if I hardcode value (eg. '100') query runs faster
> Join table21 c
> On c.something = a.something
> ...

Well, you are now searching table10 for a constant value (which can be
done once) instead of the output of the union (which has to be done for
each line of the union, so I'm not surprised that it's faster.

What is the output of `explain (analyze)` for the two queries? Is there
an obvious place where an index would help? Can you restructure the
query?

BTW, it is (at least for me) very hard to give advice on a query with
only completely abstract names like `table11` or `field4`: I have no
idea what this is supposed to do, so it's hard to tell if there is a
better way. Using `a` to refer to 3 different things doesn't help
either.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"