Re: PostgreSQL OR performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




Yes, the query should output exactly same result as in "Union" plan. I will run "slow" explain analyze now and will repost after it will complete (tomorrow?).
BTW: I'd say planner should think rows estimated as sum of "ORs" estimation minus intersection, but no more then sum or ORs (if intersection is 0). For first condition it has rows=525975, for second it has rows=2403 (with other plans, of course), so it's strange it has such a high estimation.... It's exactly 50% of full cartesian join of merge, so it does think that every second pair would succeed, that is not true.


I am sorry, I've emptied atom_match table, so one part produce 0 result, but anyway here is explain:

"Merge Join  (cost=518771.07..62884559.80 rows=1386158171 width=32) (actual time=30292.802..755751.242 rows=34749 loops=1)"
"  Merge Cond: (production.run.id = (production.company.run_id)::bigint)"
"  Join Filter: (((production.company.name)::text = (production.company.name)::text) OR (hashed subplan))"
"  ->  Sort  (cost=45474.92..45606.54 rows=52648 width=38) (actual time=562.928..595.128 rows=15507 loops=1)"
"        Sort Key: production.run.id"
"        Sort Method:  external sort  Disk: 880kB"
"        ->  Nested Loop  (cost=1184.82..39904.24 rows=52648 width=38) (actual time=90.571..530.925 rows=15507 loops=1)"
"              ->  HashAggregate  (cost=1.55..1.56 rows=1 width=8) (actual time=3.077..3.078 rows=1 loops=1)"
"                    ->  Seq Scan on run  (cost=0.00..1.55 rows=1 width=8) (actual time=3.066..3.068 rows=1 loops=1)"
"                          Filter: ((name)::text = 'test'::text)"
"              ->  Nested Loop  (cost=1183.27..39376.19 rows=52648 width=30) (actual time=87.489..484.605 rows=15507 loops=1)"
"                    ->  HashAggregate  (cost=1.55..1.56 rows=1 width=8) (actual time=0.016..0.019 rows=1 loops=1)"
"                          ->  Seq Scan on run  (cost=0.00..1.55 rows=1 width=8) (actual time=0.009..0.011 rows=1 loops=1)"
"                                Filter: ((name)::text = 'test'::text)"
"                    ->  Bitmap Heap Scan on company  (cost=1181.72..38592.03 rows=62608 width=30) (actual time=87.465..441.014 rows=15507 loops=1)"
"                          Recheck Cond: ((production.company.run_id)::bigint = production.run.id)"
"                          Filter: ((production.company.status)::text = 'unprocessed'::text)"
"                          ->  Bitmap Index Scan on comp_run  (cost=0.00..1166.07 rows=62608 width=0) (actual time=65.828..65.828 rows=15507 loops=1)"
"                                Index Cond: ((production.company.run_id)::bigint = production.run.id)"
"  ->  Materialize  (cost=469981.13..498937.42 rows=2316503 width=30) (actual time=15915.639..391938.338 rows=242752539 loops=1)"
"        ->  Sort  (cost=469981.13..475772.39 rows=2316503 width=30) (actual time=15915.599..19920.912 rows=2316503 loops=1)"
"              Sort Key: production.company.run_id"
"              Sort Method:  external merge  Disk: 104896kB"
"              ->  Seq Scan on company  (cost=0.00..58808.03 rows=2316503 width=30) (actual time=22.244..7476.954 rows=2316503 loops=1)"
"  SubPlan"
"    ->  Nested Loop  (cost=2267.65..3314.94 rows=22 width=1038) (actual time=0.009..0.009 rows=0 loops=1)"
"          ->  Hash Join  (cost=2267.65..3141.36 rows=22 width=523) (actual time=0.006..0.006 rows=0 loops=1)"
"                Hash Cond: ((atom_match.atom1_id)::integer = s1.id)"
"                ->  Seq Scan on atom_match  (cost=0.00..30.38 rows=1630 width=8) (actual time=0.002..0.002 rows=0 loops=1)"
"                      Filter: ((match_function_id)::integer = 2)"
"                ->  Hash  (cost=1292.04..1292.04 rows=12209 width=523) (never executed)"
"                      ->  Index Scan using atomstr_typ on atoms_string s1  (cost=0.00..1292.04 rows=12209 width=523) (never executed)"
"                            Index Cond: ((atom_type_id)::integer = (-1))"
"          ->  Index Scan using pk_atoms_string on atoms_string s2  (cost=0.00..7.88 rows=1 width=523) (never executed)"
"                Index Cond: (s2.id = (atom_match.atom2_id)::integer)"
"Total runtime: 755802.686 ms"

P.S. May be I've chosen wrong list and my Q better belongs to -hackers?

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux