StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPostgresql: inner join takes 70 seconds
primarykey
Id
13088407
data
AcceptedAnswerId
13292153
AnswerCount
2
ClosedDate
CommentCount
22
CommunityOwnedDate
CreationDate
2012-10-26T14:02:25.520
FavoriteCount
1
LastActivityDate
2012-11-08T15:32:18.807
LastEditDate
2012-10-26T18:06:12.657
LastEditorUserId
51178
OwnerUserId
51178
ParentId
0
PostTypeId
1
Score
2
ViewCount
1919
LastEditorDisplayName
text
Body
I have two tables - Table A : 1MM rows, AsOfDate, Id, BId (foreign key to table B) Table B : 50k rows, Id, Flag, ValidFrom, ValidTo Table A contains multiple records per day between 2011/01/01 and 2011/12/31 across 100 BId's. Table B contains multiple non overlapping (between validfrom and validto) records for 100 Bids. The task of the join will be to return the flag that was active for the BId on the given AsOfDate. <pre><code>select a.AsOfDate, b.Flag from A a inner Join B b on a.BId = b.BId and b.ValidFrom <= a.AsOfDate and b.ValidTo >= a.AsOfDate where a.AsOfDate >= 20110101 and a.AsOfDate <= 20111231 </code></pre> This query takes ~70 seconds on a very high end server (+3Ghz) with 64Gb of memory. I have indexes on every combination of field as I'm testing this - to no avail. Indexes : a.AsOfDate, a.AsOfDate+a.bId, a.bid Indexes : b.bid, b.bid+b.validfrom Also tried the range queries suggested below (62seconds) This same query on the free version of Sql Server running in a VM takes ~1 second to complete. any ideas? Postgres 9.2 Query Plan <pre><code>QUERY PLAN --------------------------------------------------------------------------------------- Aggregate (cost=8274298.83..8274298.84 rows=1 width=0) -> Hash Join (cost=1692.25..8137039.36 rows=54903787 width=0) Hash Cond: (a.bid = b.bid) Join Filter: ((b.validfrom <= a.asofdate) AND (b.validto >= a.asofdate)) -> Seq Scan on "A" a (cost=0.00..37727.00 rows=986467 width=12) Filter: ((asofdate > 20110101) AND (asofdate < 20111231)) -> Hash (cost=821.00..821.00 rows=50100 width=12) -> Seq Scan on "B" b (cost=0.00..821.00 rows=50100 width=12) </code></pre> see <a href="http://explain.depesz.com/s/1c5" rel="nofollow noreferrer">http://explain.depesz.com/s/1c5</a> for the analyze output <img src="https://i.stack.imgur.com/2CWWU.png" alt="here is the query plan from sqlserver for the same query">
Tags
<database><performance><postgresql><postgresql-performance>
Title
Postgresql: inner join takes 70 seconds
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USheadsling
UserOwnerUserId
1. USheadsling
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POPostgresql: inner join takes 70 seconds
 UserUserId
 USleonbloy
 VoteTypeVoteTypeId
 VTFavorite
2. VO
 singulars
 PostPostId
 POPostgresql: inner join takes 70 seconds
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POPostgresql: inner join takes 70 seconds
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTModeratorReview
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.