Re: Sequential scan instead of index scan

> Offhand I'd have thought that ANALYZE would gather stats on the
> date_trunc expression (because it is indexed) and then you should get
> something reasonably accurate for a comparison to a constant.
> "Reasonably accurate" meaning "not off by two orders of magnitude".
> Practically all of your runtime is going into this one indexscan,
> and TBH it seems likely you'd be better off with a seqscan there.
>
> regards, tom lane
You were right, after running ANALYZE on the temp table I eventually got
the HASH JOIN we were talking about. Here is the plan:

"Hash Join (cost=379575.54..1507341.18 rows=95142 width=128) (actual
time=3128.940..634179.270 rows=10495795 loops=1)"
" Hash Cond: (feed_all_y2012m08.ship_pos_messages.msg_id =
tmp_tbl_messages.msg_id)"
" -> Append (cost=0.00..1073525.24 rows=95142 width=128) (actual
time=37.157..599002.314 rows=18891614 loops=1)"
" -> Seq Scan on ship_pos_messages (cost=0.00..0.00 rows=1
width=100) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((date_part('day'::text, msg_date_rec) =
2::double precision) AND (date_trunc('day'::text, msg_date_rec) =
'2012-08-02 00:00:00'::timestamp without time zone))"
" -> Seq Scan on ship_a_pos_messages ship_pos_messages
(cost=0.00..0.00 rows=1 width=100) (actual time=0.000..0.000 rows=0
loops=1)"
" Filter: ((date_part('day'::text, msg_date_rec) =
2::double precision) AND (date_trunc('day'::text, msg_date_rec) =
'2012-08-02 00:00:00'::timestamp without time zone))"
" -> Index Scan using idx_ship_b_std_pos_messages_date_trunc on
ship_b_std_pos_messages ship_pos_messages (cost=0.00..48111.95
rows=4323 width=128) (actual time=37.156..23782.030 rows=808692 loops=1)"
" Index Cond: (date_trunc('day'::text, msg_date_rec) =
'2012-08-02 00:00:00'::timestamp without time zone)"
" Filter: (date_part('day'::text, msg_date_rec) = 2::double
precision)"
" -> Index Scan using idx_ship_b_ext_pos_messages_date_trunc on
ship_b_ext_pos_messages ship_pos_messages (cost=0.00..1844.30 rows=154
width=128) (actual time=42.042..1270.104 rows=28656 loops=1)"
" Index Cond: (date_trunc('day'::text, msg_date_rec) =
'2012-08-02 00:00:00'::timestamp without time zone)"
" Filter: (date_part('day'::text, msg_date_rec) = 2::double
precision)"
" -> Index Scan using idx_ship_a_pos_messages_wk0_date_trunc on
ship_a_pos_messages_wk0 ship_pos_messages (cost=0.00..1023568.99
rows=90663 width=128) (actual time=51.181..571590.415 rows=18054266
loops=1)"
" Index Cond: (date_trunc('day'::text, msg_date_rec) =
'2012-08-02 00:00:00'::timestamp without time zone)"
" Filter: (date_part('day'::text, msg_date_rec) = 2::double
precision)"
" -> Hash (cost=177590.46..177590.46 rows=12311446 width=8) (actual
time=3082.762..3082.762 rows=12311446 loops=1)"
" Buckets: 524288 Batches: 4 Memory Usage: 120316kB"
" -> Seq Scan on tmp_tbl_messages (cost=0.00..177590.46
rows=12311446 width=8) (actual time=0.022..1181.376 rows=12311446 loops=1)"
"Total runtime: 634764.596 ms"

The time looks reasonable but still quite high for the over night job I
am need it for (have to run around 30 of those). So since the join has
been shorted I think I need to do something with the rows difference
between actual and expected in the:

" -> Index Scan using idx_ship_a_pos_messages_wk0_date_trunc on
ship_a_pos_messages_wk0 ship_pos_messages (cost=0.00..1023568.99
rows=90663 width=128) (actual time=51.181..571590.415 rows=18054266
loops=1)"
" Index Cond: (date_trunc('day'::text, msg_date_rec) =
'2012-08-02 00:00:00'::timestamp without time zone)"
" Filter: (date_part('day'::text, msg_date_rec) = 2::double
precision)"

From what I understand a possible solution is to increase the stats
target for the particular column(?). Any suggestion there? I assume we
are talking about the msg_date_rec where the index is build uppon.
Finally, I do understand what you say about the Seq scan. However in
this case I have consistently about 10min per execution while the
SeqScan was giving me almost nothing at best and usually it was running
for so long that
eventually was causing my server problems...

Kind Regards
Yiannis