Re: 225 times slower

=?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= <pailloncy(at)ifrance(dot)com> writes:
> dps=# explain analyze SELECT rec_id FROM url WHERE crc32!=0 AND
> crc32=419903683 AND status IN (200,304,206) ORDER BY rec_id LIMIT 1;
> QUERY PLAN
> ------------------------------------------------------------------------
> ----------------------------------------------------------
> Limit (cost=0.00..27.95 rows=1 width=4) (actual
> time=11021.875..11021.876 rows=1 loops=1)
> -> Index Scan using url_pkey on url (cost=0.00..11625.49 rows=416
> width=4) (actual time=11021.868..11021.868 rows=1 loops=1)
> Filter: ((crc32 <> 0) AND (crc32 = 419903683) AND ((status =
> 200) OR (status = 304) OR (status = 206)))
> Total runtime: 11021.986 ms
> (4 rows)
> dps=# \q

The planner is guessing that scanning in rec_id order will produce a
matching row fairly quickly (sooner than selecting all the matching rows
and sorting them would do). It's wrong in this case, but I'm not sure
it could do better without very detailed cross-column statistics. Am I
right to guess that the rows that match the WHERE clause are not evenly
distributed in the rec_id order, but rather there are no such rows till
you get well up in the ordering?

regards, tom lane