Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Here's the table (nothing strange, has about 12 million rows).
- CREATE TABLE vehicleevent
- (
- event_id bigserial NOT NULL,
- veh_id integer NOT NULL,
- .
- .
- .
- CONSTRAINT pk_vehicleevent PRIMARY KEY (event_id),
- CONSTRAINT fk_vehicleevent_vehicle FOREIGN KEY (veh_id)
- REFERENCES vehicle (veh_id) MATCH SIMPLE
- ON UPDATE CASCADE ON DELETE CASCADE
- );
- CREATE INDEX ix_vehicleevent_veh_id
- ON vehicleevent
- USING btree
- (veh_id);
- Case A:
- No rows in table for the veh_id requested (LIMIT set to 5145 or larger by trial and error). This works as expected.
- "select count(*) from vehicleevent where veh_id=3" returns 0 rows.
- SELECT * FROM vehicleevent WHERE veh_id = 3 ORDER BY event_id
- LIMIT 5145
- Execution plan:
- "Limit (cost=83309.05..83321.91 rows=5145 width=64)"
- " -> Sort (cost=83309.05..83388.32 rows=31709 width=64)"
- " Sort Key: event_id"
- " -> Bitmap Heap Scan on vehicleevent (cost=599.32..81195.81 rows=31709 width=64)"
- " Recheck Cond: (veh_id = 3)"
- " -> Bitmap Index Scan on ix_vehicleevent_veh_id (cost=0.00..591.39 rows=31709 width=0)"
- " Index Cond: (veh_id = 3)"
- Result:
- Total query runtime: 16 ms.
- 0 rows retrieved.
- Case B:
- 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.
- "select count(*) from vehicleevent where veh_id=3" returns 0 rows.
- SELECT * FROM vehicleevent WHERE veh_id = 3 ORDER BY event_id
- LIMIT 5144
- Execution plan:
- "Limit (cost=0.00..83317.96 rows=5144 width=64)"
- " -> Index Scan using pk_vehicleevent on vehicleevent (cost=0.00..513594.34 rows=31709 width=64)"
- " Filter: (veh_id = 3)"
- Result:
- Total query runtime: 55141 ms.
- 0 rows retrieved.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement