Re: Join Bad Performance on different data types

2014-03-04 10:19 GMT+02:00 Adarsh Sharma <eddy(dot)adarsh(at)gmail(dot)com>:
>
> PLAN
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> HashAggregate (cost=10651634346.70..10651780073.12 rows=4163612
> width=64) (actual time=34375.675..34764.705 rows=751392 loops=1)
> -> Nested Loop (cost=10000000000.03..10646590270.49 rows=336271747
> width=64) (actual time=0.217..24988.534 rows=6541944 loops=1)
> -> Nested Loop (cost=10000000000.02..10012318364.23
> rows=33628639 width=116) (actual time=0.177..3427.380 rows=1431 loops=1)
> -> Nested Loop (cost=10000000000.01..10001045237.36
> rows=3368723 width=38) (actual time=0.138..3373.767 rows=1431 loops=1)
> -> Nested Loop (cost=10000000000.00..10000097742.23
> rows=340181 width=38) (actual time=0.047..2151.183 rows=418145 loops=1)
> Join Filter: (td.entity_type_id = gtt.id)
> Rows Removed by Join Filter: 1269335
> -> Seq Scan on graph5 td
> (cost=10000000000.00..10000077008.13 rows=345413 width=33) (actual
> time=0.020..1231.823 rows=421870 loops=1)
> -> Materialize (cost=0.00..9.33 rows=4
> width=13) (actual time=0.000..0.001 rows=4 loops=421870)
> -> Index Scan using geo_type_pkey on
> graph6 gtt (cost=0.00..9.31 rows=4 width=13) (actual time=0.009..0.012
> rows=4 loops=1)
> -> Index Scan using graph2_pkey on graph2 gcr
> (cost=0.01..2.69 rows=10 width=33) (actual time=0.002..0.002 rows=0
> loops=418145)
> Index Cond: (id = ANY (td.graph3_id))
> -> Index Scan using graph3_pkey on graph3 gtd
> (cost=0.01..3.25 rows=10 width=115) (actual time=0.035..0.036 rows=1
> loops=1431)
> Index Cond: (id = ANY (gcr.t_ids))
> -> Index Scan using graph1_pkey on graph1 glt (cost=0.01..18.51
> rows=10 width=55) (actual time=0.085..9.082 rows=4572 loops=1431)
> Index Cond: (id = ANY (gtd.lat_long_id_list))
> Total runtime: 34810.040 ms
>
>
> Is dere any way i can rewrite the query so that i need not to set
> seqscan-off, because i dont want to embed one more line in application
> code and also dont want to change global setting in postgresql.conf to
> disable seqscan.
>

Could you, kindly, also post `EXPLAIN` output of your original and modified
queries also, leaving out all `enable_...` setting at their defaults.
Just explain, without `analyze`.

--
Victor Y. Yegorov