Re: BUG #5120: Performance difference between running a query with named cursor and straight SELECT - Mailing list pgsql-bugs

>Steve McLellan <smclellan@mintel.com> writes:
>> Thanks! Installing 8.4 and setting cursor_tuple_fraction to 1.0 does seem
to
>> force it to execute in the same time as not using the cursor, and we'll
>> probably go with this solution (the only reason we're using cursors is to
>> avoid retrieving vast result sets through psycopg2/fetchmany). Your
>> explanation makes sense, and I'm curious to see why this particular query
>> ends up being so different, but I couldn't figure out how to run the
explain
>> - the DECLARE syntax doesn't seem to allow it.
>
> "EXPLAIN [ANALYZE] DECLARE x CURSOR ..." works for me.
>
>   regards, tom lane

Sorry, I should've tried that. The two explains are below; without cursor
then with cursor. I don't know enough to reliably say whether there's
anything wrong with either (we use default_statistics_target=100 although
the estimates don't look right for some of the query fragments), but they're
certainly very different.


========
WITHOUT CURSOR:

Hash Join  (cost=33364.36..46606.90 rows=1 width=4) (actual
time=795.690..833.577 rows=14 loops=1)
   Hash Cond: (fact_table.originator_key =
originator_dimension.originator_key)
   ->  Nested Loop  (cost=33339.89..46577.20 rows=1392 width=8) (actual
time=792.676..832.203 rows=186 loops=1)
         ->  Nested Loop  (cost=33339.89..40678.24 rows=48 width=8) (actual
time=792.653..830.017 rows=110 loops=1)
               ->  Hash Join  (cost=33339.89..40641.84 rows=66 width=12)
(actual time=467.029..790.075 rows=3884 loops=1)
                     Hash Cond: (record_type_mv.record_key =
record_dimension.record_key)
                     ->  Seq Scan on record_type_mv  (cost=0.00..7076.20
rows=30012 width=4) (actual time=0.920..270.790 rows=31056 loops=1)
                           Filter: (("Type")::text = ANY (('{"Cat 1 -
yyy","Cat 1 - www","Cat 1 - zzz"}'::character varying[])::text[]))
                     ->  Hash  (cost=33287.67..33287.67 rows=4178 width=8)
(actual time=447.819..447.819 rows=10203 loops=1)
                           ->  Bitmap Heap Scan on record_dimension
 (cost=18214.01..33287.67 rows=4178 width=8) (actual time=371.277..428.278
rows=10203 loops=1)
                                 Recheck Cond: (("Category" = 'Cat 1'::text)
AND ("Sector" = 'Sector I'::text) AND ("Dataset - A" OR "Dataset - C" OR
"Dataset - B"))
                                 Filter: (("Dataset - A" OR "Dataset - C" OR
"Dataset - B") AND ("Country" = 'USA'::text))
                                 ->  BitmapAnd  (cost=18214.01..18214.01
rows=4623 width=0) (actual time=367.336..367.336 rows=0 loops=1)
                                       ->  Bitmap Index Scan on "Category"
 (cost=0.00..3091.75 rows=123623 width=0) (actual time=53.713..53.713
rows=124053 loops=1)
                                             Index Cond: ("Category" = 'Cat
1'::text)
                                       ->  Bitmap Index Scan on "Sector"
 (cost=0.00..7525.37 rows=327577 width=0) (actual time=129.610..129.610
rows=328054 loops=1)
                                             Index Cond: ("Sector" = 'Sector
I'::text)
                                       ->  BitmapOr  (cost=7594.30..7594.30
rows=410371 width=0) (actual time=128.983..128.983 rows=0 loops=1)
                                             ->  Bitmap Index Scan on
"Dataset - A"  (cost=0.00..550.30 rows=29579 width=0) (actual
time=11.393..11.393 rows=30016 loops=1)
                                                   Index Cond: ("Dataset -
A" = true)
                                             ->  Bitmap Index Scan on
"Dataset - C"  (cost=0.00..6981.18 rows=377696 width=0) (actual
time=116.306..116.306 rows=380788 loops=1)
                                                   Index Cond: ("Dataset -
C" = true)
                                             ->  Bitmap Index Scan on
"Dataset - B"  (cost=0.00..59.69 rows=3097 width=0) (actual
time=1.272..1.272 rows=3116 loops=1)
                                                   Index Cond: ("Dataset -
B" = true)
               ->  Index Scan using date_dimension_pkey on date_dimension
"date_dimension_Published Date"  (cost=0.00..0.54 rows=1 width=4) (actual
time=0.007..0.007 rows=0 loops=3884)
                     Index Cond: ("date_dimension_Published Date".date_key =
record_dimension.published_date_key)
                     Filter: ("date_dimension_Published Date"."SQL Date" >=
(now() - '6 mons'::interval))
         ->  Index Scan using record_date on instance_core_fact fact_table
 (cost=0.00..122.48 rows=33 width=12) (actual time=0.007..0.011 rows=2
loops=110)
               Index Cond: (fact_table.record_key =
record_dimension.record_key)
   ->  Hash  (cost=24.45..24.45 rows=1 width=4) (actual time=1.004..1.004
rows=169 loops=1)
         ->  Bitmap Heap Scan on originator_dimension  (cost=20.44..24.45
rows=1 width=4) (actual time=0.313..0.662 rows=169 loops=1)
               Recheck Cond: (("Area" = ANY ('{National,"Phoenix,
AZ"}'::text[])) AND ("Originator Type" = 'NO DATA'::text))
               ->  BitmapAnd  (cost=20.44..20.44 rows=1 width=0) (actual
time=0.299..0.299 rows=0 loops=1)
                     ->  Bitmap Index Scan on publication_market_area
 (cost=0.00..8.95 rows=54 width=0) (actual time=0.165..0.165 rows=169
loops=1)
                           Index Cond: ("Area" = ANY ('{National,"Phoenix,
AZ"}'::text[]))
                     ->  Bitmap Index Scan on "Originator Type"
 (cost=0.00..11.24 rows=390 width=0) (actual time=0.121..0.121 rows=426
loops=1)
                           Index Cond: ("Originator Type" = 'NO DATA'::text)
===================
WITH CURSOR:

Nested Loop  (cost=0.00..145138.44 rows=1 width=4) (actual
time=1508.556..556939.884 rows=14 loops=1)
   Join Filter: (fact_table.originator_key =
originator_dimension.originator_key)
   ->  Index Scan using "Originator Type" on originator_dimension
 (cost=0.00..125.29 rows=1 width=4) (actual time=26.067..41.906 rows=169
loops=1)
         Index Cond: ("Originator Type" = 'NO DATA'::text)
         Filter: ("Area" = ANY ('{National,"Phoenix, AZ"}'::text[]))
   ->  Nested Loop  (cost=0.00..144995.75 rows=1392 width=8) (actual
time=1081.426..3294.915 rows=186 loops=169)
         ->  Nested Loop  (cost=0.00..139096.78 rows=48 width=8) (actual
time=1081.108..3290.440 rows=110 loops=169)
               ->  Nested Loop  (cost=0.00..124922.31 rows=3026 width=4)
(actual time=1061.766..3284.424 rows=380 loops=169)
                     ->  Seq Scan on record_dimension  (cost=0.00..122618.12
rows=4178 width=8) (actual time=0.427..3177.623 rows=10203 loops=169)
                           Filter: (("Dataset - A" OR "Dataset - C" OR
"Dataset - B") AND ("Sector" = 'Sector I'::text) AND ("Category" = 'Cat
1'::text) AND ("Country" = 'USA'::text))
                     ->  Index Scan using date_dimension_pkey on
date_dimension "date_dimension_Published Date"  (cost=0.00..0.54 rows=1
width=4) (actual time=0.007..0.007 rows=0 loops=1724307)
                           Index Cond: ("date_dimension_Published
Date".date_key = record_dimension.published_date_key)
                           Filter: ("date_dimension_Published Date"."SQL
Date" >= (now() - '6 mons'::interval))
               ->  Index Scan using record_type_mv_pkey on record_type_mv
 (cost=0.00..4.67 rows=1 width=4) (actual time=0.011..0.012 rows=0
loops=64220)
                     Index Cond: (record_type_mv.record_key =
record_dimension.record_key)
                     Filter: ((record_type_mv."Type")::text = ANY (('{"Cat 1
- yyy","Cat 1 - www","Cat 1 - zzz"}'::character varying[])::text[]))
         ->  Index Scan using record_date on instance_core_fact fact_table
 (cost=0.00..122.48 rows=33 width=12) (actual time=0.024..0.032 rows=2
loops=18590)
               Index Cond: (fact_table.record_key =
record_dimension.record_key)