Re: Bad plan using join on VALUES (and now on temp table too) - Mailing list pgsql-general

> Well, the planner probably guessed that in your case it's faster to
> scan the table than to use the index (indexes are not free). Did it
> choose wrong?

    Yes, see the other query in my post...

    id IN ( 60 values ) => 0.582 ms (bitmap scan on the index : perfect)
    join with VALUES => 45.594 ms (seq scan)

> If you disable the seqscan, does it get faster (set
> enable_seqscan=false). Make sure you run both a few times to make sure
> you're getting good results.

    Yeah, everything is in cache.
    With enable_seq to 0, it does a nested loop at 1.190 ms

> If it turns out the planner is wrong, you need to do some tuning, in
> particular random_page_cost and effective_cache_size.

    I don't want to screw my tuning (which works well for all other queries)
because of this single one !
    I modified the website to use IN(), but I thought it might be a bug in
the planner...

    I'll repost.
    I believe the planner does not consider the right plan. (see more below)

EXPLAIN ANALYZE SELECT * FROM relations WHERE child_id
IN(
8695,8743,10309,22000,22980,23016,8683,25092,13369,13377,13375,13371,13373,25126,10629,13363,13365,22820,15705,13367,8759,8783,8815,23018,8781,8765,23012,23036,23046,8803,25226,22940,8771,8769,13335,23004,22816,23062,8805,8727,13379,23064,23032,24976,8807,23024,8787,23028,8809,8735,23042,8813,8731,22964,8755,8697,8749,22974,8733,8715
 
) ;

                                                                      QUERY   
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on relations  (cost=176.93..383.43 rows=236 width=58)
(actual time=0.295..0.470 rows=350 loops=1)
    Recheck Cond: (child_id = ANY

('{8695,8743,10309,22000,22980,23016,8683,25092,13369,13377,13375,13371,13373,25126,10629,13363,13365,22820,15705,13367,8759,8783,8815,23018,8781,8765,23012,23036,23046,8803,25226,22940,8771,8769,13335,23004,22816,23062,8805,8727,13379,23064,23032,24976,8807,23024,8787,23028,8809,8735,23042,8813,8731,22964,8755,8697,8749,22974,8733,8715}'::integer[]))
    ->  Bitmap Index Scan on relations_child  (cost=0.00..176.87 rows=236
width=0) (actual time=0.279..0.279 rows=350 loops=1)
          Index Cond: (child_id = ANY

('{8695,8743,10309,22000,22980,23016,8683,25092,13369,13377,13375,13371,13373,25126,10629,13363,13365,22820,15705,13367,8759,8783,8815,23018,8781,8765,23012,23036,23046,8803,25226,22940,8771,8769,13335,23004,22816,23062,8805,8727,13379,23064,23032,24976,8807,23024,8787,23028,8809,8735,23042,8813,8731,22964,8755,8697,8749,22974,8733,8715}'::integer[]))
  Total runtime: 0.571 ms

explain analyze SELECT target.* FROM relations AS target, (VALUES
(8695::INTEGER), (8743), (10309), (22000), (22980), (23016), (8683),
(25092), (13369), (13377), (13375), (13371), (13373), (25126), (10629),
(13363), (13365), (22820), (15705), (13367), (8759), (8783), (8815),
(23018), (8781), (8765), (23012), (23036), (23046), (8803), (25226),
(22940), (8771), (8769), (13335), (23004), (22816), (23062), (8805),
(8727), (13379), (23064), (23032), (24976), (8807), (23024), (8787),
(23028), (8809), (8735), (23042), (8813), (8731), (22964), (8755), (8697),
(8749), (22974), (8733), (8715)) AS source WHERE target.child_id =
source.column1;
                                                         QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
  Hash Join  (cost=1.50..542.68 rows=216 width=58) (actual
time=0.349..44.907 rows=350 loops=1)
    Hash Cond: (target.child_id = "*VALUES*".column1)
    ->  Seq Scan on relations target  (cost=0.00..440.29 rows=26329
width=58) (actual time=0.011..8.250 rows=26334 loops=1)
    ->  Hash  (cost=0.75..0.75 rows=60 width=4) (actual time=0.076..0.076
rows=60 loops=1)
          ->  Values Scan on "*VALUES*"  (cost=0.00..0.75 rows=60 width=4)
(actual time=0.001..0.035 rows=60 loops=1)
  Total runtime: 45.048 ms

SET enable_seqscan TO 0;
SET
caillaudangers=> explain analyze SELECT target.* FROM relations AS target,
(VALUES (8695::INTEGER), (8743), (10309), (22000), (22980), (23016),
(8683), (25092), (13369), (13377), (13375), (13371), (13373), (25126),
(10629), (13363), (13365), (22820), (15705), (13367), (8759), (8783),
(8815), (23018), (8781), (8765), (23012), (23036), (23046), (8803),
(25226), (22940), (8771), (8769), (13335), (23004), (22816), (23062),
(8805), (8727), (13379), (23064), (23032), (24976), (8807), (23024),
(8787), (23028), (8809), (8735), (23042), (8813), (8731), (22964), (8755),
(8697), (8749), (22974), (8733), (8715)) AS source WHERE target.child_id =
source.column1;
                                                                 QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.00..756.21 rows=216 width=58) (actual
time=0.023..1.151 rows=350 loops=1)
    ->  Values Scan on "*VALUES*"  (cost=0.00..0.75 rows=60 width=4)
(actual time=0.002..0.041 rows=60 loops=1)
    ->  Index Scan using relations_child on relations target
(cost=0.00..12.54 rows=4 width=58) (actual time=0.005..0.013 rows=6
loops=60)
          Index Cond: (target.child_id = "*VALUES*".column1)
  Total runtime: 1.265 ms


    ------------------------------

    I stuffed the 60 integer values into a TEMP TABLE instead of using VALUES
or IN, and the plans are also very wrong :

caillaudangers=> INSERT INTO tmp VALUES (8695::INTEGER), (8743), (10309),
(22000), (22980), (23016), (8683), (25092), (13369), (13377), (13375),
(13371), (13373), (25126), (10629), (13363), (13365), (22820), (15705),
(13367), (8759), (8783), (8815), (23018), (8781), (8765), (23012),
(23036), (23046), (8803), (25226), (22940), (8771), (8769), (13335),
(23004), (22816), (23062), (8805), (8727), (13379), (23064), (23032),
(24976), (8807), (23024), (8787), (23028), (8809), (8735), (23042),
(8813), (8731), (22964), (8755), (8697), (8749), (22974), (8733), (8715);
INSERT 0 60
caillaudangers=> ANALYZE relations;
ANALYZE
caillaudangers=> ANALYZE tmp;
ANALYZE
caillaudangers=> EXPLAIN ANALYZE SELECT * FROM relations WHERE child_id IN
(SELECT id FROM tmp);
                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
  Hash IN Join  (cost=2.35..543.39 rows=195 width=58) (actual
time=0.367..36.242 rows=350 loops=1)
    Hash Cond: (relations.child_id = tmp.id)
    ->  Seq Scan on relations  (cost=0.00..440.34 rows=26334 width=58)
(actual time=0.009..15.604 rows=26334 loops=1)
    ->  Hash  (cost=1.60..1.60 rows=60 width=4) (actual time=0.065..0.065
rows=60 loops=1)
          ->  Seq Scan on tmp  (cost=0.00..1.60 rows=60 width=4) (actual
time=0.004..0.024 rows=60 loops=1)
  Total runtime: 36.396 ms
(6 lignes)

caillaudangers=> EXPLAIN ANALYZE SELECT r.* FROM relations r, tmp t WHERE
r.child_id=t.id;
                                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
  Hash Join  (cost=2.35..543.39 rows=195 width=58) (actual
time=0.403..33.120 rows=350 loops=1)
    Hash Cond: (r.child_id = t.id)
    ->  Seq Scan on relations r  (cost=0.00..440.34 rows=26334 width=58)
(actual time=0.011..14.987 rows=26334 loops=1)
    ->  Hash  (cost=1.60..1.60 rows=60 width=4) (actual time=0.061..0.061
rows=60 loops=1)
          ->  Seq Scan on tmp t  (cost=0.00..1.60 rows=60 width=4) (actual
time=0.004..0.024 rows=60 loops=1)
  Total runtime: 33.266 ms