Re: potential performance gain by query planner optimization

2010/7/21 Kneringer, Armin <Armin(dot)Kneringer(at)fabasoft(dot)com>:
> Hi Pavel,
>
> Turning hashjoin off also does the trick. (the explain output is below)
> My basic intention was to check if the query planner could be optmized to automatically improve the query processing.
> In this case all users (espacially those which are not be aware of ineffective query processing e.g. due their own queries) might profit by faster query execution.
> This is just a thought (or suggestion) for further enhancement. Evt. it will be added to the project backlog.
>

You have a problem with inadequate statistics. Somewhere optimalizer
prefer hash join (available for sets less than work_mem), but try to
store to much data to hash tables and system will to use a swap :(.

Regards
Pavel Stehule

> kind regards
> Armin
>
> For reasons of completeness the eplain output with "hashjoin off":
>
>
> # explain analyze SELECT DISTINCT t4.objid FROM fscsubfile t4, cooobject t6 WHERE t6.objid = t4.objid AND t4.fileresporgid = 573936067464397682 AND NOT EXISTS (SELECT 1 FROM ataggval q1_1, atdateval t5 WHERE q1_1.objid = t4.objid AND q1_1.attrid = 281479288456451 AND q1_1.aggrid = 0 AND t5.aggrid = q1_1.aggval AND t5.objid = t4.objid AND t5.attrid = 281479288456447 ) AND ( (t6.objclassid IN (285774255832590,285774255764301))) AND ((t4.objid > 573936097512390656 and t4.objid < 573936101807357952)) ORDER BY t4.objid;
>                                                                                  QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Unique  (cost=13639921632.59..14512729357.51 rows=1 width=8) (actual time=14172.154..14468.435 rows=64 loops=1)
>   ->  Nested Loop  (cost=13639921632.59..14512729357.51 rows=1 width=8) (actual time=14172.148..14468.364 rows=64 loops=1)
>         ->  Merge Anti Join  (cost=13639921632.59..14512729349.28 rows=1 width=8) (actual time=14092.764..14108.850 rows=108 loops=1)
>               Merge Cond: ((t4.objid = q1_1.objid) AND (t4.objid = t5.objid))
>               ->  Sort  (cost=14477.12..14490.83 rows=5486 width=8) (actual time=100.070..109.200 rows=63436 loops=1)
>                     Sort Key: t4.objid
>                     Sort Method:  quicksort  Memory: 4510kB
>                     ->  Bitmap Heap Scan on fscsubfile t4  (cost=154.42...14136.40 rows=5486 width=8) (actual time=14.645..54.176 rows=63436 loops=1)
>                           Recheck Cond: ((fileresporgid = 573936067464397682::bigint) AND (objid > 573936097512390656::bigint) AND (objid < 573936101807357952::bigint))
>                           ->  Bitmap Index Scan on ind_fscsubfile_filerespons  (cost=0.00..153.05 rows=5486 width=0) (actual time=11.438..11.438 rows=63436 loops=1)
>                                 Index Cond: ((fileresporgid = 573936067464397682::bigint) AND (objid > 573936097512390656::bigint) AND (objid < 573936101807357952::bigint))
>               ->  Materialize  (cost=13584864369.09..14272439933.58 rows=55006045159 width=16) (actual time=12914.166..13699.719 rows=1299867 loops=1)
>                     ->  Sort  (cost=13584864369.09..13722379481.99 rows=55006045159 width=16) (actual time=12914.153..13411.554 rows=1299867 loops=1)
>                           Sort Key: q1_1.objid, t5.objid
>                           Sort Method:  external merge  Disk: 47192kB
>                           ->  Nested Loop  (cost=0.00..11917516.57 rows=55006045159 width=16) (actual time=0.621..10505.130 rows=1858326 loops=1)
>                                 ->  Seq Scan on atdateval t5  (cost=0.00...294152.40 rows=1859934 width=12) (actual time=0.593..1870.182 rows=1857906 loops=1)
>                                       Filter: (attrid = 281479288456447::bigint)
>                                 ->  Index Scan using ind_ataggval on ataggval q1_1  (cost=0.00..6.20 rows=4 width=12) (actual time=0.004..0.004 rows=1 loops=1857906)
>                                       Index Cond: ((q1_1.attrid = 281479288456451::bigint) AND (q1_1.aggval = t5.aggrid))
>                                       Filter: (q1_1.aggrid = 0)
>         ->  Index Scan using cooobjectix on cooobject t6  (cost=0.00..8.22 rows=1 width=8) (actual time=3.327..3.328 rows=1 loops=108)
>               Index Cond: (t6.objid = t4.objid)
>               Filter: (t6.objclassid = ANY ('{285774255832590,285774255764301}'::bigint[]))
>  Total runtime: 14487.434 ms
>
>
>
>
>
>
> -----Original Message-----
> From: Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
> Sent: Dienstag, 20. Juli 2010 21:39
> To: Kneringer, Armin
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] potential performance gain by query planner optimization
>
> Hello
>
> 2010/7/20 Kneringer, Armin <Armin(dot)Kneringer(at)fabasoft(dot)com>:
>> Hi there.
>>
>> I think I found a potential performance gain if the query planner
>> would be optimized. All Tests has been performed with 8.4.1 (and
>> earlier versions) on CentOS 5.3 (x64)
>>
>> The following query will run on my database (~250 GB) for ca. 1600
>> seconds and the sort will result in a disk merge deploying ca. 200 GB
>> of data to the local disk (ca. 180.000 tmp-files)
>
> can you try show check explain with set enable_hashjoin to off;   ?
>
> Regards
>
> Pavel Stehule
>
>>
>> explain SELECT DISTINCT t4.objid
>> FROM fscsubfile t4, cooobject t6
>>  NOT EXISTS (
>>  WHERE t6.objid = t4.objid AND
>>  t4.fileresporgid = 573936067464397682 AND
>>   NOT EXISTS (
>>   SELECT 1
>>   FROM ataggval q1_1,
>>   atdateval t5
>>   WHERE q1_1.objid = t4.objid AND
>>   q1_1.attrid = 281479288456451 AND
>>   q1_1.aggrid = 0 AND
>>   t5.aggrid = q1_1.aggval AND
>>   t5.objid = t4.objid AND
>>   t5.attrid = 281479288456447 ) AND
>>  ((t6.objclassid IN (285774255832590,285774255764301))) AND
>>  ((t4.objid > 573936097512390656 and t4.objid < 573936101807357952))
>>  ORDER BY t4.objid;
>>
>>
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> ----------------------------------------------------------------------
>> ----------------------------------
>> Unique  (cost=2592137103.99..2592137104.00 rows=1 width=8)
>>   ->  Sort  (cost=2592137103.99..2592137104.00 rows=1 width=8)
>>         Sort Key: t4.objid
>>         ->  Nested Loop  (cost=1105592553.38..2592137103.98 rows=1
>> width=8)
>>               ->  Hash Anti Join  (cost=1105592553.38..2592137095.75
>> rows=1 width=8)
>>                     Hash Cond: ((t4.objid = q1_1.objid) AND (t4.objid
>> = t5.objid))
>>                     ->  Bitmap Heap Scan on fscsubfile t4
>> (cost=154.42...14136.40 rows=5486 width=8)
>>                           Recheck Cond: ((fileresporgid =
>> 573936067464397682::bigint) AND (objid > 573936097512390656::bigint)
>> AND (objid < 573936101807357952::bigint))
>>                           ->  Bitmap Index Scan on
>> ind_fscsubfile_filerespons  (cost=0.00..153.05 rows=5486 width=0)
>>                                 Index Cond: ((fileresporgid =
>> 573936067464397682::bigint) AND (objid > 573936097512390656::bigint)
>> AND (objid < 573936101807357952::bigint))
>>                     ->  Hash  (cost=11917516.57..11917516.57
>> rows=55006045159 width=16)
>>                           ->  Nested Loop  (cost=0.00..11917516.57
>> rows=55006045159 width=16)
>>                                 ->  Seq Scan on atdateval t5
>> (cost=0.00...294152.40 rows=1859934 width=12)
>>                                       Filter: (attrid =
>> 281479288456447::bigint)
>>                                 ->  Index Scan using ind_ataggval on
>> ataggval q1_1  (cost=0.00..6.20 rows=4 width=12)
>>                                       Index Cond: ((q1_1.attrid =
>> 281479288456451::bigint) AND (q1_1.aggval = t5.aggrid))
>>                                       Filter: (q1_1.aggrid = 0)
>>               ->  Index Scan using cooobjectix on cooobject t6
>> (cost=0.00..8.22 rows=1 width=8)
>>                     Index Cond: (t6.objid = t4.objid)
>>                     Filter: (t6.objclassid = ANY
>> ('{285774255832590,285774255764301}'::bigint[]))
>> (20 rows)
>>
>>
>> As the disks pace is limited on my test system I can't provide the
>> "explain analyze" output If I change the query as follows the query takes only 12 seconds and only needs 2 tmp files for sorting.
>> (Changed lines are marked with [!!!!!] as I don't know HTML-Mails will
>> be delivered without conversion
>>
>> explain SELECT DISTINCT t4.objid
>> FROM fscsubfile t4, cooobject t6
>> WHERE t6.objid = t4.objid AND
>> t4.fileresporgid = 573936067464397682 AND
>>   NOT EXISTS (
>>   SELECT 1
>>   FROM ataggval q1_1,
>>   atdateval t5
>>   WHERE q1_1.objid = t4.objid AND
>>   q1_1.attrid = 281479288456451 AND
>>   q1_1.aggrid = 0 AND
>>   t5.aggrid = q1_1.aggval AND
>>   t5.objid = q1_1.objid AND                 [!!!!!]
>>   t5.attrid = 281479288456447 ) AND
>>   ((t6.objclassid IN (285774255832590,285774255764301))) AND
>>   ((t4.objid > 573936097512390656 and t4.objid < 573936101807357952))
>>  ORDER BY t4.objid;
>>
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> ----------------------------------------------------------------------
>> ---------------------- Unique  (cost=918320.29..971968.88 rows=1
>> width=8)
>>   ->  Nested Loop  (cost=918320.29..971968.88 rows=1 width=8)
>>         ->  Merge Anti Join  (cost=918320.29..971960.65 rows=1
>> width=8)
>>               Merge Cond: (t4.objid = q1_1.objid)
>>               ->  Index Scan using ind_fscsubfile_filerespons on
>> fscsubfile t4  (cost=0.00..19016.05 rows=5486 width=8)
>>                     Index Cond: ((fileresporgid =
>> 573936067464397682::bigint) AND (objid > 573936097512390656::bigint)
>> AND (objid < 573936101807357952::bigint))
>>               ->  Materialize  (cost=912418.42..956599.36 rows=22689
>> width=8)
>>                     ->  Merge Join  (cost=912418.42..956372.47
>> rows=22689 width=8)
>>                           Merge Cond: ((t5.objid = q1_1.objid) AND
>> (t5.aggrid = q1_1.aggval))
>>                           ->  Sort  (cost=402024.80..406674.63
>> rows=1859934 width=12)
>>                                 Sort Key: t5.objid, t5.aggrid
>>                                 ->  Bitmap Heap Scan on atdateval t5
>> (cost=43749.07..176555.24 rows=1859934 width=12)
>>                                       Recheck Cond: (attrid =
>> 281479288456447::bigint)
>>                                       ->  Bitmap Index Scan on
>> ind_atdateval  (cost=0.00..43284.08 rows=1859934 width=0)
>>                                             Index Cond: (attrid =
>> 281479288456447::bigint)
>>                           ->  Materialize  (cost=510392.25..531663.97
>> rows=1701738 width=12)
>>                                 ->  Sort  (cost=510392.25..514646.59
>> rows=1701738 width=12)
>>                                       Sort Key: q1_1.objid,
>> q1_1.aggval
>>                                       ->  Bitmap Heap Scan on ataggval
>> q1_1  (cost=44666.00..305189.47 rows=1701738 width=12)
>>                                             Recheck Cond: (attrid =
>> 281479288456451::bigint)
>>                                             Filter: (aggrid = 0)
>>                                             ->  Bitmap Index Scan on
>> ind_ataggval  (cost=0.00..44240.56 rows=1860698 width=0)
>>                                                   Index Cond: (attrid
>> = 281479288456451::bigint)
>>         ->  Index Scan using cooobjectix on cooobject t6
>> (cost=0.00..8.22 rows=1 width=8)
>>               Index Cond: (t6.objid = t4.objid)
>>               Filter: (t6.objclassid = ANY
>> ('{285774255832590,285774255764301}'::bigint[]))
>> (26 rows)
>>
>> explain analyze SELECT DISTINCT t4.objid FROM fscsubfile t4, cooobject
>> t6 WHERE t6.objid = t4.objid AND t4.fileresporgid = 573936067464397682
>> AND
>>  NOT EXISTS (
>>  SELECT 1
>>  FROM ataggval q1_1,
>>  atdateval t5
>>  WHERE q1_1.objid = t4.objid AND
>>  q1_1.attrid = 281479288456451 AND
>>  q1_1.aggrid = 0 AND
>>  t5.aggrid = q1_1.aggval AND
>>  t5.objid = q1_1.objid AND                 [!!!!!]
>>  t5.attrid = 281479288456447 ) AND
>> ((t6.objclassid IN (285774255832590,285774255764301))) AND ((t4.objid
>> > 573936097512390656 and t4.objid < 573936101807357952)) ORDER BY
>> t4.objid;
>>
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> ----------------------------------------------------------------------
>> -----------------------------------
>> Unique  (cost=918320.29..971968.88 rows=1 width=8) (actual
>> time=12079.598..12083.048 rows=64 loops=1)
>>   ->  Nested Loop  (cost=918320.29..971968.88 rows=1 width=8) (actual
>> time=12079.594..12083.010 rows=64 loops=1)
>>         ->  Merge Anti Join  (cost=918320.29..971960.65 rows=1
>> width=8) (actual time=12037.524..12081.989 rows=108 loops=1)
>>               Merge Cond: (t4.objid = q1_1.objid)
>>               ->  Index Scan using ind_fscsubfile_filerespons on
>> fscsubfile t4  (cost=0.00..19016.05 rows=5486 width=8) (actual
>> time=0.073..83.498 rows=63436 loops=1)
>>                     Index Cond: ((fileresporgid =
>> 573936067464397682::bigint) AND (objid > 573936097512390656::bigint)
>> AND (objid < 573936101807357952::bigint))
>>               ->  Materialize  (cost=912418.42..956599.36 rows=22689
>> width=8) (actual time=8866.253..11753.055 rows=1299685 loops=1)
>>                     ->  Merge Join  (cost=912418.42..956372.47
>> rows=22689 width=8) (actual time=8866.246..11413.397 rows=1299685
>> loops=1)
>>                           Merge Cond: ((t5.objid = q1_1.objid) AND
>> (t5.aggrid = q1_1.aggval))
>>                           ->  Sort  (cost=402024.80..406674.63
>> rows=1859934 width=12) (actual time=3133.362..3774.076 rows=1299685
>> loops=1)
>>                                 Sort Key: t5.objid, t5.aggrid
>>                                 Sort Method:  external merge  Disk:
>> 47192kB
>>                                 ->  Bitmap Heap Scan on atdateval t5
>> (cost=43749.07..176555.24 rows=1859934 width=12) (actual
>> time=282.454..1079.038 rows=1857906 loops=1)
>>                                       Recheck Cond: (attrid =
>> 281479288456447::bigint)
>>                                       ->  Bitmap Index Scan on
>> ind_atdateval  (cost=0.00..43284.08 rows=1859934 width=0) (actual
>> time=258.749...258.749 rows=1857906 loops=1)
>>                                             Index Cond: (attrid =
>> 281479288456447::bigint)
>>                           ->  Materialize  (cost=510392.25..531663.97
>> rows=1701738 width=12) (actual time=5732.872..6683.784 rows=1299685
>> loops=1)
>>                                 ->  Sort  (cost=510392.25..514646.59
>> rows=1701738 width=12) (actual time=5732.866..6387.188 rows=1299685
>> loops=1)
>>                                      Sort Key: q1_1.objid, q1_1.aggval
>>                                       Sort Method:  external merge
>> Disk: 39920kB
>>                                       ->  Bitmap Heap Scan on ataggval
>> q1_1  (cost=44666.00..305189.47 rows=1701738 width=12) (actual
>> time=1644.983..3634.044 rows=1857906 loops=1)
>>                                             Recheck Cond: (attrid =
>> 281479288456451::bigint)
>>                                             Filter: (aggrid = 0)
>>                                             ->  Bitmap Index Scan on
>> ind_ataggval  (cost=0.00..44240.56 rows=1860698 width=0) (actual
>> time=1606.325..1606.325 rows=1877336 loops=1)
>>                                                   Index Cond: (attrid
>> = 281479288456451::bigint)
>>         ->  Index Scan using cooobjectix on cooobject t6
>> (cost=0.00..8.22 rows=1 width=8) (actual time=0.009..0.009 rows=1
>> loops=108)
>>               Index Cond: (t6.objid = t4.objid)
>>               Filter: (t6.objclassid = ANY
>> ('{285774255832590,285774255764301}'::bigint[]))
>> Total runtime: 12108.663 ms
>> (29 rows)
>>
>>
>> Another way to optimize my query is to change it as follows:
>> (Once again changes are marked with [!!!!!]
>>
>> explain SELECT DISTINCT t4.objid
>> FROM fscsubfile t4, cooobject t6
>> WHERE t6.objid = t4.objid AND
>> t4.fileresporgid = 573936067464397682 AND
>>   NOT EXISTS (
>>   SELECT 1
>>   FROM ataggval q1_1,
>>   atdateval t5
>>   WHERE q1_1.objid = t5.objid AND                 [!!!!!]
>>   q1_1.attrid = 281479288456451 AND
>>   q1_1.aggrid = 0 AND
>>   t5.aggrid = q1_1.aggval AND
>>   t5.objid = t4.objid AND
>>   t5.attrid = 281479288456447 ) AND
>>  ((t6.objclassid IN (285774255832590,285774255764301))) AND
>>  ((t4.objid > 573936097512390656 and t4.objid < 573936101807357952))
>>  ORDER BY t4.objid;
>>
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> ----------------------------------------------------------------------
>> ---------------------- Unique  (cost=916978.86..969139.72 rows=1
>> width=8)
>>   ->  Nested Loop  (cost=916978.86..969139.72 rows=1 width=8)
>>         ->  Merge Anti Join  (cost=916978.86..969131.49 rows=1
>> width=8)
>>               Merge Cond: (t4.objid = t5.objid)
>>               ->  Index Scan using ind_fscsubfile_filerespons on
>> fscsubfile t4  (cost=0.00..19016.05 rows=5486 width=8)
>>                     Index Cond: ((fileresporgid =
>> 573936067464397682::bigint) AND (objid > 573936097512390656::bigint)
>> AND (objid < 573936101807357952::bigint))
>>               ->  Materialize  (cost=912418.42..956599.36 rows=22689
>> width=8)
>>                     ->  Merge Join  (cost=912418.42..956372.47
>> rows=22689 width=8)
>>                           Merge Cond: ((t5.objid = q1_1.objid) AND
>> (t5.aggrid = q1_1.aggval))
>>                           ->  Sort  (cost=402024.80..406674.63
>> rows=1859934 width=12)
>>                                 Sort Key: t5.objid, t5.aggrid
>>                                 ->  Bitmap Heap Scan on atdateval t5
>> (cost=43749.07..176555.24 rows=1859934 width=12)
>>                                       Recheck Cond: (attrid =
>> 281479288456447::bigint)
>>                                       ->  Bitmap Index Scan on
>> ind_atdateval  (cost=0.00..43284.08 rows=1859934 width=0)
>>                                             Index Cond: (attrid =
>> 281479288456447::bigint)
>>                           ->  Materialize  (cost=510392.25..531663.97
>> rows=1701738 width=12)
>>                                 ->  Sort  (cost=510392.25..514646.59
>> rows=1701738 width=12)
>>                                       Sort Key: q1_1.objid,
>> q1_1.aggval
>>                                       ->  Bitmap Heap Scan on ataggval
>> q1_1  (cost=44666.00..305189.47 rows=1701738 width=12)
>>                                             Recheck Cond: (attrid =
>> 281479288456451::bigint)
>>                                             Filter: (aggrid = 0)
>>                                             ->  Bitmap Index Scan on
>> ind_ataggval  (cost=0.00..44240.56 rows=1860698 width=0)
>>                                                   Index Cond: (attrid
>> = 281479288456451::bigint)
>>         ->  Index Scan using cooobjectix on cooobject t6
>> (cost=0.00..8.22 rows=1 width=8)
>>               Index Cond: (t6.objid = t4.objid)
>>               Filter: (t6.objclassid = ANY
>> ('{285774255832590,285774255764301}'::bigint[]))
>> (26 rows)
>>
>>
>> explain analyze SELECT DISTINCT t4.objid FROM fscsubfile t4, cooobject
>> t6 WHERE t6.objid = t4.objid AND t4.fileresporgid = 573936067464397682
>> AND
>>  NOT EXISTS (
>>  SELECT 1
>>  FROM ataggval q1_1,
>>  atdateval t5
>>  WHERE q1_1.objid = t5.objid AND                 [!!!!!]
>>  q1_1.attrid = 281479288456451 AND
>>  q1_1.aggrid = 0 AND
>>  t5.aggrid = q1_1.aggval AND
>>  t5.objid = t4.objid AND
>>  t5.attrid = 281479288456447 ) AND
>> ((t6.objclassid IN (285774255832590,285774255764301))) AND ((t4.objid
>> > 573936097512390656 and t4.objid < 573936101807357952)) ORDER BY
>> t4.objid;
>>
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> ----------------------------------------------------------------------
>> -----------------------------------
>>
>> Unique  (cost=916978.86..969139.72 rows=1 width=8) (actual
>> time=12102.964..12106.409 rows=64 loops=1)
>>   ->  Nested Loop  (cost=916978.86..969139.72 rows=1 width=8) (actual
>> time=12102.959..12106.375 rows=64 loops=1)
>>         ->  Merge Anti Join  (cost=916978.86..969131.49 rows=1
>> width=8) (actual time=12060.916..12105.374 rows=108 loops=1)
>>               Merge Cond: (t4.objid = t5.objid)
>>               ->  Index Scan using ind_fscsubfile_filerespons on
>> fscsubfile t4  (cost=0.00..19016.05 rows=5486 width=8) (actual
>> time=0.080..81.397 rows=63436 loops=1)
>>                     Index Cond: ((fileresporgid =
>> 573936067464397682::bigint) AND (objid > 573936097512390656::bigint)
>> AND (objid < 573936101807357952::bigint))
>>               ->  Materialize  (cost=912418.42..956599.36 rows=22689
>> width=8) (actual time=8874.492..11778.254 rows=1299685 loops=1)
>>                     ->  Merge Join  (cost=912418.42..956372.47
>> rows=22689 width=8) (actual time=8874.484..11437.175 rows=1299685
>> loops=1)
>>                           Merge Cond: ((t5.objid = q1_1.objid) AND
>> (t5.aggrid = q1_1.aggval))
>>                           ->  Sort  (cost=402024.80..406674.63
>> rows=1859934 width=12) (actual time=3117.555..3756.062 rows=1299685
>> loops=1)
>>                                 Sort Key: t5.objid, t5.aggrid
>>                                 Sort Method:  external merge  Disk:
>> 39920kB
>>                                 ->  Bitmap Heap Scan on atdateval t5
>> (cost=43749.07..176555.24 rows=1859934 width=12) (actual
>> time=289.475..1079.624 rows=1857906 loops=1)
>>                                       Recheck Cond: (attrid =
>> 281479288456447::bigint)
>>                                       ->  Bitmap Index Scan on
>> ind_atdateval  (cost=0.00..43284.08 rows=1859934 width=0) (actual
>> time=265.720...265.720 rows=1857906 loops=1)
>>                                             Index Cond: (attrid =
>> 281479288456447::bigint)
>>                           ->  Materialize  (cost=510392.25..531663.97
>> rows=1701738 width=12) (actual time=5756.915..6707.864 rows=1299685
>> loops=1)
>>                                 ->  Sort  (cost=510392.25..514646.59
>> rows=1701738 width=12) (actual time=5756.909..6409.819 rows=1299685
>> loops=1)
>>                                       Sort Key: q1_1.objid,
>> q1_1.aggval
>>                                       Sort Method:  external merge
>> Disk: 39920kB
>>                                       ->  Bitmap Heap Scan on ataggval
>> q1_1  (cost=44666.00..305189.47 rows=1701738 width=12) (actual
>> time=1646.955..3628.918 rows=1857906 loops=1)
>>                                             Recheck Cond: (attrid =
>> 281479288456451::bigint)
>>                                             Filter: (aggrid = 0)
>>                                             ->  Bitmap Index Scan on
>> ind_ataggval  (cost=0.00..44240.56 rows=1860698 width=0) (actual
>> time=1608.233..1608.233 rows=1877336 loops=1)
>>                                                   Index Cond: (attrid
>> = 281479288456451::bigint)
>>         ->  Index Scan using cooobjectix on cooobject t6
>> (cost=0.00..8.22 rows=1 width=8) (actual time=0.008..0.009 rows=1
>> loops=108)
>>               Index Cond: (t6.objid = t4.objid)
>>               Filter: (t6.objclassid = ANY
>> ('{285774255832590,285774255764301}'::bigint[]))
>> Total runtime: 12129.613 ms
>> (29 rows)
>>
>>
>>
>> As the query performs in roughly 12 seconds in both (changed) cases
>> you might advise to change my queries :-) (In fact we are working on
>> this) As the primary performance impact can also be reproduced in a small database (querytime > 1 minute) I checked this issue on MS-SQL server and Oracle. On MSSQL server there is no difference in the execution plan if you change the query an the performance is well. Oralce shows a slightly difference but the performance is also well.
>> As I mentioned we are looking forward to change our query but in my
>> opinion there could be a general performance gain if this issue is
>> addressed. (especially if you don't know you run into this issue on
>> the query performance is sufficient enough)
>>
>> greets
>> Armin
>>
>> --
>> Sent via pgsql-performance mailing list
>> (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>