From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ioannis Anagnostopoulos <ioannis@anatec.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequential scan instead of index scan
Date: Mon, 06 Aug 2012 23:13:27 -0400
Message-ID: <5168.1344309207@sss.pgh.pa.us> (raw)
In-Reply-To: <50203F5A.3040805@anatec.com>
References: <501FDDD5.2000106@anatec.com>
<501FDFCD.2030806@oneit.com.au>
<501FE1C9.6010209@anatec.com>
<10966.1344267248@sss.pgh.pa.us>
<50203F5A.3040805@anatec.com>
Ioannis Anagnostopoulos <ioannis@anatec.com> writes:
> On 06/08/2012 16:34, Tom Lane wrote:
>> What you should probably be looking for is a hash join plan.
> ...
> Which is a Merge join and not a hash. Any ideas how to make it a hash join?
You might need to ANALYZE the temp table, if you didn't already. Also
it might be that you need to increase work_mem enough to fit the temp
table into memory.
Another thing that's bothering me is that the rowcount estimates are so
far off, particularly this one:
> " -> Index Scan using
> idx_ship_a_pos_messages_wk0_date_trunc on ship_a_pos_messages_wk0
> ship_pos_messages (cost=0.00..1109877.86 rows=99313 width=128) (actual
> time=0.029..435784.376 rows=18499560 loops=1)"
> " Index Cond: (date_trunc('day'::text, msg_date_rec)
> = '2012-08-01 00:00:00'::timestamp without time zone)"
> " Filter: (date_part('day'::text, msg_date_rec) =
> 1::double precision)"
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ioannis@anatec.com
Subject: Re: Sequential scan instead of index scan
In-Reply-To: <5168.1344309207@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox