Re: does the query planner consider work_mem?

Ants -- you're on the right track: i tried your suggestion and found
that at times during subsequent executions the performance will drop
down to about 6 seconds.
though periodically it jumps back up to about 3 minutes, and there's
no other DB server usage during these times (i.e. i'm the only one
connected).
i should note, however, that the 8.2 version has not once been slow
with this query.

so it may be a cache issue, though i have other queries, also slow in
the 8.4 version, and continue to be slow, no matter how many times i
re-run them.
in most cases (so far) i'm able to re-write them to be faster in the
8.4 version, but it's a different formulation than the 8.2 version.
(i.e. if i take the 8.2 version and run it on 8.4 it's slow and
vice-versa).
this means i need to maintain/test two versions of each query during
the migration, which is a nightmare for me.
(BTW -- forgot to mention this, but the listing__tag table in the
examples has ~35 million rows, and there's an index on each column.)

as an example: i re-wrote the query to use the full join version, and
on the 8.4 version (after a fresh restart of the server) the plan was
the same as before:

explain analyze select lt.listing_id from listing__tag lt,
listing__tag x where lt.tag_name = x.tag_name and x.listing_id =
91032370 group by lt.listing_id;

Group (cost=485411.21..490831.04 rows=488868 width=4) (actual
time=5474.662..5636.718 rows=272166 loops=1)
-> Sort (cost=485411.21..488121.13 rows=1083967 width=4) (actual
time=5474.658..5560.040 rows=295990 loops=1)
Sort Key: lt.listing_id
Sort Method: external merge Disk: 4048kB
-> Nested Loop (cost=35.44..347109.96 rows=1083967 width=4)
(actual time=3.908..5090.687 rows=295990 loops=1)
-> Index Scan using listing__tag___listing_id on
listing__tag x (cost=0.00..283.44 rows=71 width=10) (actu
al time=0.050..0.086 rows=11 loops=1)
Index Cond: (listing_id = 91032370)
-> Bitmap Heap Scan on listing__tag lt
(cost=35.44..4868.36 rows=1322 width=14) (actual time=8.664..456.08
7 rows=26908 loops=11)
Recheck Cond: (lt.tag_name = x.tag_name)
-> Bitmap Index Scan on listing__tag___tag_name
(cost=0.00..35.11 rows=1322 width=0) (actual time=7.
065..7.065 rows=26908 loops=11)
Index Cond: (lt.tag_name = x.tag_name)
Total runtime: 5656.900 ms

this top-level join query on the 8.2 machine, despite there being only
a single join, performs abysmally (which is why i had to coerce the
8.2 query planner to do the correct nesting in the first place):

Group (cost=4172480.61..4232829.15 rows=37744 width=4)
-> Sort (cost=4172480.61..4202654.88 rows=12069709 width=4)
Sort Key: lt.listing_id
-> Hash Join (cost=1312642.10..1927697.87 rows=12069709 width=4)
Hash Cond: (x.tag_name = lt.tag_name)
-> Index Scan using listing__tag___listing_id on
listing__tag x (cost=0.00..3682.79 rows=951 width=13)
Index Cond: (listing_id = 91032370)
-> Hash (cost=613609.60..613609.60 rows=36151960 width=17)
-> Seq Scan on listing__tag lt
(cost=0.00..613609.60 rows=36151960 width=17)

(only EXPLAIN here on this query as i stopped the first attempt to
EXPLAIN ANALYZE after about 15 minutes :-/ )

cheers,

-m

p.s. on the 8.4 version EXPLAIN ANALYZE *still* tells me that an
external merge on disk is happening, despite my setting of work_mem to
a full 16 MB this time.
does anyone know how to resolve this? or should i even worry about it?

On Wed, May 30, 2012 at 2:25 PM, Ants Aasma <ants(at)cybertec(dot)at> wrote:
> On Wed, May 30, 2012 at 8:57 PM, Murat Tasan <mmuurr(at)gmail(dot)com> wrote:
>> any insights here?
>
> Have you tried running the slow option multiple times? According to
> the explain output all of the time was accounted to the bitmap heap
> scan. For the second explain plan the same node was fast. It looks to
> me as the first explain on 8.4 was slow because the data was still on
> disk. Raising work mem doubled the speed of the sort from 800ms to
> 400ms.
>
> Regards,
> Ants Aasma
> --
> Cybertec Schönig & Schönig GmbH
> Gröhrmühlgasse 26
> A-2700 Wiener Neustadt
> Web: http://www.postgresql-support.de