Postgres Pro
Downloads
Home   >   mailing lists

Query on indexed table too slow - Mailing list pgsql-novice

From Shmagi Kavtaradze
Subject Query on indexed table too slow
Date
Msg-id CAHY6mazJytja8Dut-WFcXt+3sYnxCthq0tuQRn-4nmgK_A8JDw@mail.gmail.com
Whole thread Raw
Responses Re: Query on indexed table too slow  (Fábio Moreira <fabio@dias.moreira.nom.br>)
List pgsql-novice
Tree view
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?

pgsql-novice by date:

Previous
From: Sándor Daku
Date:
Subject: Re: Trigger function failure
Next
From: Fábio Moreira
Date:
Subject: Re: Query on indexed table too slow