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?
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 |