Guest User

Untitled

a guest
Jan 5th, 2012
29
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. db=# explain analyze SELECT COUNT(*) FROM "Posts" JOIN "Posts" "parent" ON "Posts"."ParentPostId" = "parent"."Id" WHERE ("parent"."PosterId" = 25132) AND ("Posts"."PosterId" != 25132);
  2. QUERY PLAN
  3.  
  4. ------------------------------------------------------------------------------------------------------------------------------------------------------------------
  5. Aggregate (cost=954463.54..954463.55 rows=1 width=0) (actual time=23701.884..23701.884 rows=1 loops=1)
  6. -> Nested Loop (cost=0.00..952387.64 rows=830356 width=0) (actual time=28.105..23692.084 rows=16712 loops=1)
  7. -> Index Scan using "Posts_PosterId_idx" on "Posts" parent (cost=0.00..138975.02 rows=41139 width=4) (actual time=17.015..6163.959 rows=45919 loops=1)
  8. Index Cond: ("PosterId" = 25132)
  9. -> Index Scan using "Post_ParentPostId_idx" on "Posts" (cost=0.00..19.52 rows=20 width=4) (actual time=0.336..0.379 rows=0 loops=45919)
  10. Index Cond: ("Posts"."ParentPostId" = parent."Id")
  11. Filter: ("Posts"."PosterId" <> 25132)
  12. Total runtime: 23701.947 ms
  13. (8 rows)
  14.  
  15.  
  16. db=# explain analyze SELECT COUNT(*) FROM "Posts" JOIN "Posts" "parent" ON "Posts"."ParentPostId" = "parent"."Id" WHERE ("parent"."PosterId" = 25132) AND ("Posts"."PosterId" != 25132);
  17. QUERY PLAN
  18.  
  19. ---------------------------------------------------------------------------------------------------------------------------------------------------------------
  20. Aggregate (cost=954463.54..954463.55 rows=1 width=0) (actual time=283.440..283.441 rows=1 loops=1)
  21. -> Nested Loop (cost=0.00..952387.64 rows=830356 width=0) (actual time=0.069..280.291 rows=16712 loops=1)
  22. -> Index Scan using "Posts_PosterId_idx" on "Posts" parent (cost=0.00..138975.02 rows=41139 width=4) (actual time=0.033..34.377 rows=45919 loops=1)
  23. Index Cond: ("PosterId" = 25132)
  24. -> Index Scan using "Post_ParentPostId_idx" on "Posts" (cost=0.00..19.52 rows=20 width=4) (actual time=0.004..0.004 rows=0 loops=45919)
  25. Index Cond: ("Posts"."ParentPostId" = parent."Id")
  26. Filter: ("Posts"."PosterId" <> 25132)
  27. Total runtime: 283.503 ms
  28. (8 rows)
  29.  
  30.  
  31. db=# \d "Posts";
  32. Table "public.Posts"
  33. Column | Type | Modifiers
  34. ------------------+-----------------------------+------------------------------------------------------
  35. Id | integer | not null default nextval('"Posts_Id_seq"'::regclass)
  36. PosterId | integer | not null
  37. PostDate | timestamp with time zone | not null
  38. LastChangeDate | timestamp(0) with time zone |
  39. Revision | integer |
  40. LayerId | integer | not null
  41. Title | text | not null
  42. Body | text | not null
  43. ThreadId | integer | not null
  44. ParentPostId | integer |
  45. TotalPunishments | smallint |
  46. Indexes:
  47. "Posts_pkey" PRIMARY KEY, btree ("Id") CLUSTER
  48. "Posts_Id_ThreadId_key" UNIQUE, btree ("Id", "ThreadId")
  49. "Post_ParentPostId_idx" btree ("ParentPostId")
  50. "Posts_PosterId_idx" btree ("PosterId")
  51. "Posts_ThreadId_idx" btree ("ThreadId")
  52. Foreign-key constraints:
  53. "Posts_Id_Revision_fkey" FOREIGN KEY ("Id", "Revision") REFERENCES "Revisions"("PostId", "Number") DEFERRABLE INITIALLY DEFERRED
  54. "Posts_LayerId_fkey" FOREIGN KEY ("LayerId") REFERENCES "Layers"("Id")
  55. "Posts_ParentPostId_fkey" FOREIGN KEY ("ParentPostId") REFERENCES "Posts"("Id")
  56. "Posts_PosterId_fkey" FOREIGN KEY ("PosterId") REFERENCES "Users"("Id") ON DELETE RESTRICT
  57. "Posts_ThreadId_fkey" FOREIGN KEY ("ThreadId") REFERENCES "Threads"("Id") ON DELETE RESTRICT
  58. Referenced by:
  59. TABLE ""Posts"" CONSTRAINT "Posts_ParentPostId_fkey" FOREIGN KEY ("ParentPostId") REFERENCES "Posts"("Id")
  60. TABLE ""PunishmentTransfers"" CONSTRAINT "PunishmentTransfers_ParentPostId_fkey" FOREIGN KEY ("OldParentPostId") REFERENCES "Posts"("Id") ON UPDATE RESTRICT ON DELETE RESTRICT
  61. TABLE ""Punishments"" CONSTRAINT "Punishments_PostId_fkey" FOREIGN KEY ("PostId") REFERENCES "Posts"("Id") ON UPDATE RESTRICT ON DELETE RESTRICT
  62. TABLE ""Revisions"" CONSTRAINT "Revisions_PostId_fkey" FOREIGN KEY ("PostId") REFERENCES "Posts"("Id") ON DELETE RESTRICT
  63. TABLE ""Threads"" CONSTRAINT "Threads_FirstPostId_fkey" FOREIGN KEY ("FirstPostId") REFERENCES "Posts"("Id")
  64. TABLE ""Threads"" CONSTRAINT "Threads_LastPostId_fkey" FOREIGN KEY ("LastPostId") REFERENCES "Posts"("Id") ON UPDATE RESTRICT ON DELETE RESTRICT
  65. TABLE ""Threads_ReadMarkers"" CONSTRAINT "Threads_ReadMarkers_PostId_fkey" FOREIGN KEY ("PostId") REFERENCES "Posts"("Id") ON UPDATE RESTRICT ON DELETE RESTRICT
  66.  
  67.  
  68. db=#
RAW Paste Data