Hi Igor,
thanks for the reply. The sequential scan on user_2_competition wasn't my main-problem. What really suprised me was the sequential scan on table user, which is a sequential scan over one million rows.
Hash Left Join (cost=111357.64..126222.29 rows=41396 width=42) (actual time=1982.543..2737.331 rows=41333 loops=1)
Hash Cond: ((uc.user_id)::text = (
u.id)::text)
-> Seq Scan on user_2_competition uc (cost=0.00..4705.21 rows=41396 width=33) (actual time=0.019..89.691 rows=41333 loops=1)
Filter: ((competition_id)::text = '3cc1cb9b3ac132ad013ad01316040001'::text)
Rows Removed by Filter: 80684
-> Hash (cost=90074.73..90074.73 rows=999673 width=42) (actual time=1977.604..1977.604 rows=999673 loops=1)
Buckets: 2048 Batches: 128 Memory Usage: 589kB
-> Seq Scan on "user" u (cost=0.00..90074.73 rows=999673 width=42) (actual time=0.004..1178.827 rows=999673 loops=1) <-- This sequential scan is strange.
IMHO the reason for the sequential scan on user is, that it is faster than an index-scan for 41333 rows. I've tried the same query using a different competition id with much less participants (about 1700). That query has a query plan as expected:
Nested Loop Left Join (cost=0.00..21385.59 rows=1684 width=42) (actual time=1.317..147.781 rows=1757 loops=1)
-> Seq Scan on user_2_competition uc (cost=0.00..7026.93 rows=1684 width=33) (actual time=1.262..92.339 rows=1757 loops=1)
Filter: ((competition_id)::text = '3cc1cb963b988f12013bc737b4590001'::text)
-> Index Scan using user_pkey on "user" u (cost=0.00..8.51 rows=1 width=42) (actual time=0.030..0.031 rows=1 loops=1757)
Index Cond: ((id)::text = (uc.user_id)::text)
Total runtime: 148.068 ms
regards
Dieter
Happy Skiing!
Dieter Rehbein
Software Architect |
dieter....@skiline.cc
Skiline Media GmbH
Lakeside B03
9020 Klagenfurt, Austria
fon:
+43 463 249445-800
fax:
+43 463 249445-102
"Erlebe Skifahren neu!"
CONFIDENTIALITY: This e-mail and any attachments are confidential and may also be privileged. If you are not the designated recipient, please notify the sender immediately by reply e-mail and destroy all copies (digital and paper). Any unauthorized disclosure, distribution, copying, storage or use of the information contained in this e-mail or any attachments is strictly prohibited and may be unlawful.
LEGAL: Skiline Media GmbH - Managing Director: Michael Saringer