This is some good stuff and I can use the explain analyze going forward.
But I can't get these VALUES queries to work.
I checked and I am on version 8.1. but I think from the docs that I
should still be able to do this.
queue=> select t.* from test t, (values(4, 23,84884,1,324234)) as v
where t.value = v.column1;
ERROR: syntax error at or near ")" at character 54
LINE 1: ...ect t.* from test t, (values(4, 23,84884,1,324234)) as v
whe...
^
Not sure if the carat is in the right place on this email, but it's
the second ')' that's the problem.
On Apr 6, 2007, at 2:26 PM, Listmail wrote:
On Fri, 06 Apr 2007 18:45:15 +0200, Markus Schiltknecht
<markus@xxxxxxxxxx> wrote:
Hi,
tom wrote:
Initially it seems that the WHERE IN (...) approach takes a turn
for the worse when the list gets very large.
Since I use this a lot on webpages, I thought maybe a little
benchmark is in order ?
CREATE TABLE test (id SERIAL PRIMARY KEY, value TEXT NOT NULL );
INSERT INTO test (value) SELECT * FROM generate_series( 1, 1000000 );
CREATE INDEX test_value ON test( value );
ANALYZE test;
My script runs EXPLAIN ANALYZE 10 times and keeps the fastest
timing, then displays it.
Then it executes the query enough times so that 1 second is
elapsed (also fetching the results), then prints the real runtime.
Well, the table is clustered (since it was created from
generate_series) so of course, bitmap index scan rules.
Get the first 300 rows of the table using column 'value' which is
an indexed TEXT column.
(using 'id' which is an integer is a bit faster)
SELECT * FROM test WHERE value IN <300 values>
Bitmap Heap Scan on test (cost=1225.83..2317.08 rows=300 width=13)
(actual time=3.736..3.807 rows=300 loops=1)
Recheck Cond: (value = ANY ('{<300 values>}'::text[]))
-> Bitmap Index Scan on test_value (cost=0.00..1225.75 rows=300
width=0) (actual time=3.717..3.717 rows=300 loops=1)
Index Cond: (value = ANY ('{<300 values>}'::text[]))
Explain Analyze runtime: 1 x 3.896 ms = 3.896 ms
Real runtime: 1 x 6.027 ms = 6.027 ms (timed on 257 iterations)
----------------------------------------
SELECT t.* FROM test t, ( VALUES <300 values> ) AS v WHERE
t.value=v.column1
Nested Loop (cost=0.00..2447.27 rows=300 width=13) (actual
time=0.035..4.724 rows=300 loops=1)
-> Values Scan on "*VALUES*" (cost=0.00..3.75 rows=300
width=32) (actual time=0.002..0.219 rows=300 loops=1)
-> Index Scan using test_value on test t (cost=0.00..8.13
rows=1 width=13) (actual time=0.013..0.014 rows=1 loops=300)
Index Cond: (t.value = "*VALUES*".column1)
Explain Analyze runtime: 1 x 4.814 ms = 4.814 ms
Real runtime: 1 x 6.786 ms = 6.786 ms (timed on 208 iterations)
----------------------------------------
SELECT * FROM test WHERE value='1'
Index Scan using test_value on test (cost=0.00..8.40 rows=1
width=13) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: (value = '1'::text)
Explain Analyze runtime: 300 x 0.032 ms = 9.600 ms
Real runtime: 300 x 0.149 ms = 44.843 ms (timed on 31251 iterations)
Now if we ask for 300 random rows out of the million in the table,
which is a lot more likely situation...
SELECT * FROM test WHERE value IN <300 values>
Bitmap Heap Scan on test (cost=1225.83..2317.08 rows=300 width=13)
(actual time=4.516..4.945 rows=300 loops=1)
Recheck Cond: (value = ANY ('{<300 values>}'::text[]))
-> Bitmap Index Scan on test_value (cost=0.00..1225.75 rows=300
width=0) (actual time=4.451..4.451 rows=300 loops=1)
Index Cond: (value = ANY ('{<300 values>}'::text[]))
Explain Analyze runtime: 1 x 5.034 ms = 5.034 ms
Real runtime: 1 x 7.278 ms = 7.278 ms (timed on 199 iterations)
----------------------------------------
SELECT t.* FROM test t, ( VALUES <300 values> ) AS v WHERE
t.value=v.column1
Nested Loop (cost=0.00..2447.27 rows=300 width=13) (actual
time=0.046..5.503 rows=300 loops=1)
-> Values Scan on "*VALUES*" (cost=0.00..3.75 rows=300
width=32) (actual time=0.001..0.200 rows=300 loops=1)
-> Index Scan using test_value on test t (cost=0.00..8.13
rows=1 width=13) (actual time=0.016..0.016 rows=1 loops=300)
Index Cond: (t.value = "*VALUES*".column1)
Explain Analyze runtime: 1 x 5.625 ms = 5.625 ms
Real runtime: 1 x 7.572 ms = 7.572 ms (timed on 178 iterations)
Doing a query per row is a lot slower than it appears in EXPLAIN
ANALYZE when you add all the overhead. In fact it sucks. That was
ecpected of course. I could tell you about when I got this php/
mysql website where the previous developer's idea of a join was a
select in a PHP for() loop because "joins are slow". It was hell.
I tried to query 1, 2, 10, 1000, 10k and 100k random rows (thats
abuse) and :
- one query per row is (obviously) the slowest unless you select
only one row
- IN reverts to a single index scan if it contains one value, else
it always uses a bitmap scan
- VALUES join uses nested loop until about 100k rows where it
switches to hash join
I also tried on a real table with many columns. In that case,
there are less tuples per page, and bitmap scans are more efficient
than nested loops, so IN wins.
So, IN() does not turn to crap anymore like it used to !
However, if you are asking for this question because you don't
want to use temporary tables, it seems that temp tables have been
upgraded so they are now usable even in a webpage (well, not ALL
webpages, but that big nasty slow webpage, you know which one I'm
talking about).
---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
