Monday, July 13, 2009

What's more expensive IN or BETWEEN statement

This morning I spend a couple of hours tuning and testing different SQL statements to find the fastest way to fetch some data for a real time application.

While doing this I discovered that the IN query cost slightly more than the BETWEEN query.
It's not a lot, but still something.

IN query:


explain select sample_name,class,date_of_import,date from samples where visible = 'TRUE' and sample_name like '%q%' and date in( '5/26/2009 12:00:00 AM', '6/11/2009 12:00:00 AM','5/26/2009 12:00:00 AM','2009-06-01 12:00:00 AM') order by 1 desc


result:

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=8126.07..8126.42 rows=139 width=37)
Sort Key: sample_name
-> Seq Scan on samples (cost=0.00..8121.12 rows=139 width=37)
Filter: (((sample_name)::text ~~ '%q%'::text) AND ((visible)::text = 'TRUE'::text) AND (date = ANY ('{"2009-05-26 00:00:00","2009-06-11 00:00:00","2009-05-26 00:00:00","2009-06-01 00:00:00"}'::timestamp without time zone[])))

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

[Executed: 7/13/09 1:11:46 PM PDT ] [Execution: 2/ms]





BETWEEN query:


explain select sample_name,class,date_of_import,date from samples where visible = 'TRUE' and sample_name like '%q%' and date between '5/26/2009 12:00:00 AM' and '6/11/2009 12:00:00 AM' order by 1 desc


Result:


QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=8123.15..8123.32 rows=67 width=37)
Sort Key: sample_name
-> Seq Scan on samples (cost=0.00..8121.12 rows=67 width=37)
Filter: (((sample_name)::text ~~ '%q%'::text) AND (date >= '2009-05-26 00:00:00'::timestamp without time zone) AND (date <= '2009-06-11 00:00:00'::timestamp without time zone) AND ((visible)::text = 'TRUE'::text))

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

[Executed: 7/13/09 12:53:32 PM PDT ] [Execution: 2/ms]

No comments:

Post a Comment