StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy does the following Postgres SQL query take so long?
primarykey
Id
6174565
data
AcceptedAnswerId
6176855
AnswerCount
3
ClosedDate
CommentCount
7
CommunityOwnedDate
CreationDate
2011-05-30T09:37:59.433
FavoriteCount
0
LastActivityDate
2011-05-30T13:23:19.167
LastEditDate
2011-05-30T09:39:45.643
LastEditorUserId
326807
OwnerUserId
144152
ParentId
0
PostTypeId
1
Score
5
ViewCount
3577
LastEditorDisplayName
text
Body
The raw query is as follows <pre><code>SELECT "TIME", "TRADEPRICE" FROM "YEAR" where "DATE"='2010-03-01' and "SECURITY"='STW.AX' AND "TIME" < '10:16:00' AND "TYPE" = 'TRADE' ORDER BY "TIME" ASC LIMIT 3 </code></pre> I have built three indices as follows <pre><code>Columns "DATE" DESC NULLS LAST Columns "SECURITY" DESC NULLS LAST Columns "TIME" DESC NULLS LAST </code></pre> I do not index TYPE because it only takes one of two possible values Explain analyze produces the following <pre><code>"Limit (cost=50291.28..50291.28 rows=3 width=16) (actual time=1794484.566..1794484.567 rows=3 loops=1)" " -> Sort (cost=50291.28..50291.29 rows=4 width=16) (actual time=1794484.562..1794484.563 rows=3 loops=1)" " Sort Key: "TIME"" " Sort Method: top-N heapsort Memory: 25kB" " -> Bitmap Heap Scan on "YEAR" (cost=48569.54..50291.24 rows=4 width=16) (actual time=1794411.662..1794484.498 rows=20 loops=1)" " Recheck Cond: (("SECURITY" = 'STW.AX'::bpchar) AND ("DATE" = '2010-03-01'::date))" " Filter: (("TIME" < '10:16:00'::time without time zone) AND ("TYPE" = 'TRADE'::bpchar))" " -> BitmapAnd (cost=48569.54..48569.54 rows=430 width=0) (actual time=1794411.249..1794411.249 rows=0 loops=1)" " -> Bitmap Index Scan on security_desc (cost=0.00..4722.94 rows=166029 width=0) (actual time=1793917.506..1793917.506 rows=1291933 loops=1)" " Index Cond: ("SECURITY" = 'STW.AX'::bpchar)" " -> Bitmap Index Scan on date_desc (cost=0.00..43846.35 rows=2368764 width=0) (actual time=378.698..378.698 rows=2317130 loops=1)" " Index Cond: ("DATE" = '2010-03-01'::date)" "Total runtime: 1794485.224 ms" </code></pre> The database is about 1 billion rows running on Core2Quad with 8gig RAM on Ubuntu 64bit. Surely this query should not take half an hour
Tags
<sql><postgresql>
Title
Why does the following Postgres SQL query take so long?
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USreporter
UserOwnerUserId
1. USdeltanovember
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POWhy does the following Postgres SQL query take so long?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POWhy does the following Postgres SQL query take so long?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POWhy does the following Postgres SQL query take so long?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
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.