Advertisement
Guest User

Untitled

a guest
Jan 27th, 2011
345
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.82 KB | None | 0 0
  1. Here's the table (nothing strange, has about 12 million rows).
  2.  
  3. CREATE TABLE vehicleevent
  4. (
  5. event_id bigserial NOT NULL,
  6. veh_id integer NOT NULL,
  7. .
  8. .
  9. .
  10. CONSTRAINT pk_vehicleevent PRIMARY KEY (event_id),
  11. CONSTRAINT fk_vehicleevent_vehicle FOREIGN KEY (veh_id)
  12. REFERENCES vehicle (veh_id) MATCH SIMPLE
  13. ON UPDATE CASCADE ON DELETE CASCADE
  14. );
  15. CREATE INDEX ix_vehicleevent_veh_id
  16. ON vehicleevent
  17. USING btree
  18. (veh_id);
  19.  
  20. Case A:
  21. No rows in table for the veh_id requested (LIMIT set to 5145 or larger by trial and error). This works as expected.
  22. "select count(*) from vehicleevent where veh_id=3" returns 0 rows.
  23.  
  24. SELECT * FROM vehicleevent WHERE veh_id = 3 ORDER BY event_id
  25. LIMIT 5145
  26.  
  27. Execution plan:
  28. "Limit (cost=83309.05..83321.91 rows=5145 width=64)"
  29. " -> Sort (cost=83309.05..83388.32 rows=31709 width=64)"
  30. " Sort Key: event_id"
  31. " -> Bitmap Heap Scan on vehicleevent (cost=599.32..81195.81 rows=31709 width=64)"
  32. " Recheck Cond: (veh_id = 3)"
  33. " -> Bitmap Index Scan on ix_vehicleevent_veh_id (cost=0.00..591.39 rows=31709 width=0)"
  34. " Index Cond: (veh_id = 3)"
  35.  
  36. Result:
  37. Total query runtime: 16 ms.
  38. 0 rows retrieved.
  39.  
  40. Case B:
  41. No rows in table for the veh_id requested (LIMIT set to 5144 or smaller). This is very slow and causes intensive physical disk access.
  42. "select count(*) from vehicleevent where veh_id=3" returns 0 rows.
  43.  
  44. SELECT * FROM vehicleevent WHERE veh_id = 3 ORDER BY event_id
  45. LIMIT 5144
  46.  
  47. Execution plan:
  48. "Limit (cost=0.00..83317.96 rows=5144 width=64)"
  49. " -> Index Scan using pk_vehicleevent on vehicleevent (cost=0.00..513594.34 rows=31709 width=64)"
  50. " Filter: (veh_id = 3)"
  51.  
  52. Result:
  53. Total query runtime: 55141 ms.
  54. 0 rows retrieved.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement