hello,
there is other performance problem on this request.
If you analyse query plan, you see that most of the time are lost during
sequencial scan, and you have 2 seq scan.
You have to create other indexes to match the request.
Postgresq is totally dependant on index to reach is performance.
Regarding gist or btree, I personnaly had better performance with btree.
Regards
david
Matthew Wakeling a écrit :
I have been doing some queries that are best answered with GiST
indexes, however I have found that their performance is a little
lacking. I thought I would do a direct comparison on a level playing
field. Here are two EXPLAIN ANALYSE results for the same query, with
two different indexes. The two indexes are identical except that one
is btree and the other GiST.
Here is the query:
SELECT *
FROM
location l1,
location l2,
gene,
primer
WHERE
l1.subjectid <> l2.subjectid
AND l1.objectid = l2.objectid
AND l1.subjectid = gene.id
AND l2.subjectid = primer.id
AND l2.intermine_start <= l1.intermine_start
AND l2.intermine_end >= l1.intermine_start
Here is the btree index:
CREATE INDEX location_object_start ON location (objectid,
intermine_start);
QUERY PLAN
----------------------------------------------------------------------
Hash Join
(cost=26213.16..135980894.76 rows=3155740824 width=484)
(actual time=2799.260..14256.588 rows=2758 loops=1)
Hash Cond: (l1.subjectid = gene.id)
-> Nested Loop
(cost=0.00..4364485.01 rows=8891802645 width=324)
(actual time=9.748..10418.807 rows=390695 loops=1)
Join Filter: (l1.subjectid <> l2.subjectid)
-> Nested Loop
(cost=0.00..446862.58 rows=572239 width=259)
(actual time=9.720..4226.117 rows=211880 loops=1)
-> Seq Scan on primer
(cost=0.00..15358.80 rows=211880 width=194)
(actual time=9.678..579.877 rows=211880 loops=1)
-> Index Scan using location__key_all on location l2
(cost=0.00..2.00 rows=3 width=65)
(actual time=0.004..0.007 rows=1 loops=211880)
Index Cond: (l2.subjectid = primer.id)
-> Index Scan using location_object_start on location l1
(cost=0.00..3.85 rows=150 width=65)
(actual time=0.005..0.012 rows=3 loops=211880)
Index Cond: ((l1.objectid = l2.objectid) AND
(l2.intermine_start <= l1.intermine_start) AND (l2.intermine_end >=
l1.intermine_start))
-> Hash
(cost=20496.96..20496.96 rows=457296 width=160)
(actual time=2788.698..2788.698 rows=457296 loops=1)
-> Seq Scan on gene
(cost=0.00..20496.96 rows=457296 width=160)
(actual time=0.038..1420.604 rows=457296 loops=1)
Total runtime: 14263.846 ms
(13 rows)
Here is the GiST index:
CREATE INDEX location_object_start_gist ON location USING gist
(objectid, intermine_start);
QUERY PLAN
------------------------------------------------------------------------
Hash Join
(cost=26213.16..136159960.32 rows=3155740824 width=484)
(actual time=2576.109..2300486.267 rows=2758 loops=1)
Hash Cond: (l1.subjectid = gene.id)
-> Nested Loop
(cost=0.00..4543550.56 rows=8891802645 width=324)
(actual time=366.121..2296668.740 rows=390695 loops=1)
Join Filter: (l1.subjectid <> l2.subjectid)
-> Nested Loop
(cost=0.00..446862.58 rows=572239 width=259)
(actual time=362.774..13423.443 rows=211880 loops=1)
-> Seq Scan on primer
(cost=0.00..15358.80 rows=211880 width=194)
(actual time=319.559..1296.907 rows=211880 loops=1)
-> Index Scan using location__key_all on location l2
(cost=0.00..2.00 rows=3 width=65)
(actual time=0.041..0.045 rows=1 loops=211880)
Index Cond: (l2.subjectid = primer.id)
-> Index Scan using location_object_start_gist on location l1
(cost=0.00..4.16 rows=150 width=65)
(actual time=3.354..10.757 rows=3 loops=211880)
Index Cond: ((l1.objectid = l2.objectid) AND
(l2.intermine_start <= l1.intermine_start) AND (l2.intermine_end >=
l1.intermine_start))
-> Hash
(cost=20496.96..20496.96 rows=457296 width=160)
(actual time=2157.914..2157.914 rows=457296 loops=1)
-> Seq Scan on gene
(cost=0.00..20496.96 rows=457296 width=160)
(actual time=3.904..1206.907 rows=457296 loops=1)
Total runtime: 2300510.674 ms
(13 rows)
The query plans are identical except in the type of index used, but
there is a factor of a few hundred in execute time. Is this the kind
of factor that would be expected, or is there something amiss? Is this
seen as something that might be improved in the future?
Matthew
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
