I am using PostgreSQL 9.4.4
. I have a query like this:
SELECT COUNT(*) FROM A,B WHERE A.a = B.b
a and b are the Primary Keys of tables A and B, so there are B-indexes on a & b
By default, PostgreSQL will use seq-scan on AB and use hash join, I force it to do the index-scan and index-only-scan.
The result showed that, seq scan is much faster than the other two, it takes more time to do the full scan on a,b for index-scan and index-only-scan.
EXPLAIN ANALYZE SELECT COUNT(*) FROM journal,paper WHERE journal.paper_id = paper.paper_id;
Can someone explain it?
Thank you so much!
explain (analyze, buffers, verbose)
for both executions