Query on indexed table too slow

From: Shmagi Kavtaradze <kavtaradze(dot)s(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Query on indexed table too slow
Date: 2016-03-07 17:12:29
Message-ID: CAHY6mazJytja8Dut-WFcXt+3sYnxCthq0tuQRn-4nmgK_A8JDw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have a table with 46230 rows(1 doc, 4623 sentences and 10 chunks for each
sentence):

create table chunks(
doc varchar,
sentenceid int,
chunkid int,
chunk bit(10)
);

With the query I want to compare sentence chunks with other sentence chunks
that have same chunkid:

SELECT
a.sentenceid,
b.sentenceid, a.chunkid,
Length(Replace(Cast(a.chunk & b.chunk AS TEXT), '0', ''))::float /
Length(a.chunk)::float
FROM chunks2 a
INNER JOIN chunks2 b
ON a.sentenceid < b.sentenceid and a.chunkid = b.chunkid;

I ran explain analyze on unindexed table, composite index and both indexed
separately,but time is the same for all:

Indexed on (sentenceid, chunkid):

Hash Join (cost=1335.17..4549476.28 rows=71249559 width=26) (actual
time=144.376..1156178.110 rows=106837530 loops=1)
Hash Cond: (a.chunkid = b.chunkid)
Join Filter: (a.sentenceid < b.sentenceid)
Rows Removed by Join Filter: 106883760
-> Seq Scan on chunks2 a (cost=0.00..757.30 rows=46230 width=15)
(actual time=0.039..77.275 rows=46230 loops=1)
-> Hash (cost=757.30..757.30 rows=46230 width=15) (actual
time=142.954..142.954 rows=46230 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2680kB
-> Seq Scan on chunks2 b (cost=0.00..757.30 rows=46230 width=15)
(actual time=0.031..64.340 rows=46230 loops=1)
Planning time: 1.209 ms
Execution time: 1212779.012 ms

I know they have the same operations and no index was used. Where is my
mistake and how to speed up query with index? Or how to use indexes
efficiently in my case?

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Fábio Moreira 2016-03-08 00:20:22 Re: Query on indexed table too slow
Previous Message Sándor Daku 2016-03-07 09:49:25 Re: Trigger function failure