Andrus wrote: > Both queries return same result (19) and return same data. > Pattern query is a much slower (93 sec) than equality check (13 sec). > How to fix this ? > Using 8.1.4, utf-8 encoding, et-EE locale They're different queries. The fact that they return the same results is a coincidence. This > WHERE rid.toode = '99000010' Is a different condition to this > WHERE rid.toode like '99000010%' You aren't going to get the same plans. Anyway, I think the problem is in the dok JOIN rid bit look: > "Aggregate (cost=43.09..43.10 rows=1 width=0) (actual > time=12674.675..12674.679 rows=1 loops=1)" > " -> Nested Loop (cost=29.57..43.08 rows=1 width=0) (actual > time=2002.045..12673.645 rows=19 loops=1)" > " -> Nested Loop (cost=29.57..37.06 rows=1 width=24) (actual > time=2001.922..12672.344 rows=19 loops=1)" > "Aggregate (cost=15.52..15.53 rows=1 width=0) (actual > time=92966.501..92966.505 rows=1 loops=1)" > " -> Nested Loop (cost=0.00..15.52 rows=1 width=0) (actual > time=24082.032..92966.366 rows=19 loops=1)" > " -> Nested Loop (cost=0.00..9.50 rows=1 width=24) (actual > time=24081.919..92965.116 rows=19 loops=1)" These are the same but the times are different. I'd be very surprised if you can reproduce these times reliably. Can I give you some wider-ranging suggestions Andrus? 1. Fix the vacuuming issue in your hash-join question. 2. Monitor the system to make sure you know if/when disk activity is high. 3. *Then* start to profile individual queries and look into their plans. Change the queries one at a time and monitor again. Otherwise, it's very difficult to figure out whether changes you make are effective. HTH -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
