Basically, $subject is causing us not to properly find matching
extended stats in this case.
The attached patch fixes it.
The following test cases is an example of the misbehaviour. Note
rows=1 vs rows=98 in the Gather node.
create table ab (a varchar, b varchar);
insert into ab select (x%1000)::varchar, (x%10000)::Varchar from
generate_Series(1,1000000)x;
create statistics ab_a_b_stats (dependencies) on a,b from ab;
analyze ab;
-- Unpatched
explain analyze select * from ab where a = '1' and b = '1';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..12466.10 rows=1 width=7) (actual
time=0.441..90.515 rows=100 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on ab (cost=0.00..11466.00 rows=1 width=7)
(actual time=1.081..74.944 rows=33 loops=3)
Filter: (((a)::text = '1'::text) AND ((b)::text = '1'::text))
Rows Removed by Filter: 333300
Planning time: 0.184 ms
Execution time: 105.878 ms
(8 rows)
-- Patched
explain analyze select * from ab where a = '1' and b = '1';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..12475.80 rows=98 width=7) (actual
time=1.076..92.595 rows=100 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on ab (cost=0.00..11466.00 rows=41 width=7)
(actual time=0.491..77.833 rows=33 loops=3)
Filter: (((a)::text = '1'::text) AND ((b)::text = '1'::text))
Rows Removed by Filter: 333300
Planning time: 2.175 ms
Execution time: 106.326 ms
(8 rows)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers