Re: [SQL] Re: How to optimize a query... -- Extreme performance difference, same EXPLAIN

Tom Lane wrote:

> > I originally had this query with an OR clause instead of the UNION,
> > which made it amazingly impossibly slow, with the UNION it's far faster
> > however it's still very slow,(10,000+ rows in both stables), is there a
> > way to speed this up?
>
> Hmm. What does EXPLAIN give as the query plan if you use the OR
> approach? The UNION method requires much more work than a decent OR
> plan would need; as you can see from the query plan, it has to sort and
> unique-ify the results of the two sub-selects in order to discard
> duplicate tuples. If you don't mind seeing the same tuple twice when
> it matches on both PO fields, you could use UNION ALL rather than UNION
> to avoid the sort step. But that's just a hack...
>

Explain seems to be telling a different story now... It used to give a
bunch of
sequential scans, when I used UNION it gave less hash joins, I thought the
less
hash joins might be better(And they were, one would take hours & run out of
memory
the other would take an hour & not run out of memory)... I have a stripped
down version
of the query I need, the UNION takes:
! system usage stats:
! 938.603740 elapsed 914.250000 user 1.140000 system sec
! [914.260000 user 1.140000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 3799/13766 [3915/13873] page faults/reclaims, 654 [654] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 0 read, 0 written, buffer hit rate =
100.00%
! Local blocks: 0 read, 0 written, buffer hit rate =
0.00%
! Direct blocks: 688 read, 716 written

When I use OR:
! system usage stats:
! 706.158704 elapsed 698.970000 user 0.370000 system sec
! [698.980000 user 0.370000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 1025/6810 [1062/6906] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 232 read, 0 written, buffer hit rate =
84.61%
! Local blocks: 0 read, 0 written, buffer hit rate =
0.00%
! Direct blocks: 0 read, 0 written

>
> I think the problem might be that Postgres doesn't know how to handle
> ORed join clauses very efficiently. Something to work on in the future.
>

I think that may of been because I didn' tknow to VACUUM ANALYZE,
however today I found a much more disturbing thing, if I simply change the
order of the SELECT clause I can change a 9.69 second query into a 605 second
query,
below is the example:

SELECT po_id, <- PK of po
material.name, <- Notice material name
here.
ticket_pk <- PK of tickets

FROM po,tickets,material
WHERE po_id=material_po
AND po.units_id=tickets.units_id
AND po.material_id=material.material_id
;
! system usage stats:
! 315.427223 elapsed 315.010000 user 0.250000 system sec
! [315.020000 user 0.250000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 645/6717 [682/6812] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 144 read, 0 written, buffer hit rate =
89.66%
! Local blocks: 0 read, 0 written, buffer hit rate =
0.00%
! Direct blocks: 0 read, 0 written

select po_id,ticket_pk,material.name

FROM po,tickets,material

WHERE po_id=material_po AND
po.units_id=tickets.units_id AND
po.material_id=material.material_id;

! system usage stats:
! 2.290408 elapsed 1.890000 user 0.250000 system sec
! [1.900000 user 0.250000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 73/6691 [110/6786] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 0 read, 0 written, buffer hit rate =
10
0.00%
! Local blocks: 0 read, 0 written, buffer hit rate =
0.
00%
! Direct blocks: 0 read, 0 written

315 seconds vs 2.3 seconds? Is there some light you can shed on
how queries are processed under PostgreSQL? They both have the
exact same EXPLAIN:

Hash Join (cost=5773.47 size=24494486 width=36)
-> Hash Join (cost=899.69 size=101128 width=26)
-> Seq Scan on po (cost=530.38 size=10860 width=10)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on material (cost=5.47 size=105 width=16)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on tickets (cost=878.78 size=19872 width=10)

The only GUESS I can come up with is that It's joining po & material then
tickets,
vs po & tickets then material, the latter one would be more efficient, since
po & material
are going to knock out lots from po, so there would be less joining against
material which
will have a component in material for all po. I have indexes on all involved
rows.

>
> > I have another query that joins the result of this with 5 other
> > tables, unfortunately that one takes like 10 minutes...
>
> How long does it take to EXPLAIN that query? Postgres' optimizer has
> some bugs that cause it to take an unreasonable amount of time to plan
> a query that joins more than a few tables. You can tell whether it's
> the planning or execution time that's the problem by comparing EXPLAIN
> runtime to the actual query. (The bugs are fixed in 6.5, btw. In the
> meantime a workaround is to reduce the GEQO threshold to less than the
> number of tables in the query that's giving you trouble.)
>
> regards, tom lane

The explain takes a few seconds, maybe 10... It's definately a runtime
thing,
one time I ran the silly thing and it sucked up 192M of RAM(64M Physical +
128M
swap) then died... Sigh. I'm slowly working my way back up to it, I just
bought
128M extra RAM... If you could shed some light on the extreme performance
difference for a simple change of order in the SELECT part of it I'd
appreciate it....
I don't think PostgreSQL is using my indexes very well, or at all... :(

Sorry about the length of the message, I wanted to include as much detail

as possible.

David Secret
MIS Director
Kearney Development Co., Inc.