From: Franck Routier <franck.routier@axege.com>
To: pgsql-performance@postgresql.org
Subject: Re: Fast distinct not working as expected
Date: Thu, 17 Apr 2014 19:17:48 +0200
Message-ID: <53500CBC.9030002@axege.com> (raw)
In-Reply-To: <CAMkU=1wTdkHmNkt9euasQux8LZ=JbWBcVb+5y1=XH3cYCpPTZw@mail.gmail.com>
References: <534FEF1D.5010706@axege.com>
<CAMkU=1wTdkHmNkt9euasQux8LZ=JbWBcVb+5y1=XH3cYCpPTZw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi,
>
> That is not equivalent to a distinct. There must be more to it than that.
Indeed, this query is used in a loop:
CREATE OR REPLACE FUNCTION small_distinct(IN tablename character
varying, IN fieldname character varying, IN sample anyelement DEFAULT
''::character varying)
RETURNS SETOF anyelement AS
$BODY$
BEGIN
EXECUTE 'SELECT '||fieldName||' FROM '||tableName||' ORDER BY
'||fieldName
||' LIMIT 1' INTO result;
WHILE result IS NOT NULL LOOP
RETURN NEXT;
EXECUTE 'SELECT '||fieldName||' FROM '||tableName
||' WHERE '||fieldName||' > $1 ORDER BY ' || fieldName || '
LIMIT 1'
INTO result USING result;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
Since we have the problem, some iteration of the query are still quick
(< 1ms), but others are long (> 5s).
>
>
>
>
>
>
> My best guess would be that the index got stuffed full of entries for
> rows that are not visible, either because they are not yet committed,
> or have been deleted but are not yet vacuumable. Do you have any
> long-lived transactions?
There has been a delete on the table (about 20% of the records). Then a
manual VACUUM.
We have recreated the index, but it did not help.
In the explain analyze output, the index scan begins at 5798.912. What
can be happening before that ?
Index Scan using vsn_idx on dwhinv (cost=0.00..302591122.05
rows=267473826 width=12) (actual time=5798.912..5798.912 rows=1 loops=1)
(Notice the delay is not planning itself, as explain is instantaneous)
>
>
>
> - postgresql Version 8.4
>
>
> Newer versions have better diagnostic tools. An explain (analyze,
> buffers) would be nice, especially with track_io_timing on.
Yep, we certainly would like to, but this is a distant prod box, with no
access to an online upgrade source, and no planned upgrade for now :-((
Regards,
Franck
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: franck.routier@axege.com
Subject: Re: Fast distinct not working as expected
In-Reply-To: <53500CBC.9030002@axege.com>
* 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