Skip site navigation (1) Skip section navigation (2)

Re: 225 times slower

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Pailloncy Jean-Gérard <pailloncy(at)ifrance(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: 225 times slower
Date: 2004-04-22 04:15:13
Message-ID: 19515.1082607313@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
=?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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2004-04-22 04:23:24
Subject: Re: Wierd context-switching issue on Xeon patch for 7.4.1
Previous:From: Dave CramerDate: 2004-04-22 03:18:47
Subject: Re: Wierd context-switching issue on Xeon patch for 7.4.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group