pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pailloncy Jean-Gérard <pailloncy@ifrance.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 225 times slower
Date: Thu, 22 Apr 2004 00:15:13 -0400
Message-ID: <19515.1082607313@sss.pgh.pa.us> (raw )
In-Reply-To: <AC3987B4-92ED-11D8-B5ED-000A95DE2550@ifrance.com >
References: <C0EA9494-8E10-11D8-BBA5-000A95DE2550@ifrance.com >
<AC3987B4-92ED-11D8-B5ED-000A95DE2550@ifrance.com >
=?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= <pailloncy@ifrance.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
view thread (3+ messages) latest in thread
Message-ID: <19515.1082607313@sss.pgh.pa.us>
Permalink: ../19515.1082607313@sss.pgh.pa.us/
Also on: postgresql.org/message-id/19515.1082607313@sss.pgh.pa.us
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, pailloncy@ifrance.com
Subject: Re: 225 times slower
In-Reply-To: <19515.1082607313@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox