Re: [SQL] OFFSET impact on Performance???

Поиск
Список
Период
Сортировка
От Oleg Bartunov
Тема Re: [SQL] OFFSET impact on Performance???
Дата
Msg-id Pine.GSO.4.62.0501271642280.6701@ra.sai.msu.su
обсуждение исходный текст
Ответ на Re: [SQL] OFFSET impact on Performance???  (PFC <lists@boutiquenumerique.com>)
Ответы Re: [SQL] OFFSET impact on Performance???  (PFC <lists@boutiquenumerique.com>)
Список pgsql-performance
On Thu, 27 Jan 2005, PFC wrote:

>
>
>> for example,
>> http://www.sai.msu.su/~megera/postgres/gist/code/7.3/README.intarray
>> see OPERATIONS and EXAMPLE USAGE:
>>
>
>     Thanks, I already know this documentation and have used intarray
> before (I find it absolutely fabulous in the right application, it has a
> great potential for getting out of tight situations which would involve huge
> unmanageable pivot or attributes tables). Its only drawback is that the gist
> index creation time is slow and sometimes just... takes forever until the
> disk is full.
>     However, it seems that integer && integer[] does not exist :

Try intset(id) && int[]. intset is an undocumented function :)
I'm going to add intset() to README.

>
>> SELECT * FROM table WHERE id && int[]
>
> explain analyze select * from temp t where id && (
> '{1,2,3,4,5,6,7,8,9,10,11,12}'::integer[] );
> ERREUR:  L'operateur n'existe pas : integer && integer[]
> ASTUCE : Aucun operateur correspond au nom donne et aux types d'arguments.
> Vous devez ajouter des conversions explicites de type.
>
>     I have already used this type of intarray indexes, but you have to
> create a special gist index with the gist__int_ops on the column, and the
> column has to be an array. In my case the column is just a SERIAL PRIMARY
> KEY, and should stay this way, and I don't want to create a functional index
> in array[id] just for this feature ; so I guess I can't use the && operator.
> Am I mistaken ? My index is the standard btree here.
>         It would be nice if the =ANY() could use the index just like
> IN does ; besides at planning time the length of the array is known which
> makes it behave quite just like IN().
>
>     So I'll use either an EXECUTE'd plpgsql-generated query (IN (....)) ,
> which I don't like because it's a kludge ; or this other solution which I
> find more elegant :
>
> CREATE OR REPLACE FUNCTION tools.array_srf( INTEGER[] )
>        RETURNS SETOF INTEGER        RETURNS NULL ON NULL INPUT
> LANGUAGE plpgsql        AS
> $$
> DECLARE
>     _data    ALIAS FOR $1;
>     _i        INTEGER;
> BEGIN
>     FOR _i IN 1..icount(_data) LOOP
>         RETURN NEXT _data[_i];
>     END LOOP;
>     RETURN;
> END;
> $$;
>
> -----------------------------------------------------------------------------------
> explain analyze select * from temp t where id =ANY(
> '{1,2,3,4,5,6,7,8,9,10,11,12}' );
> Seq Scan on "temp" t  (cost=0.00..5165.52 rows=65536 width=8) (actual
> time=0.030..173.319 rows=12 loops=1)
>   Filter: (id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12}'::integer[]))
> Total runtime: 173.391 ms
>
> -----------------------------------------------------------------------------------
> explain analyze select * from temp t where id IN( 1,2,3,4,5,6,7,8,9,10,11,12
> );
> Index Scan using temp_pkey, temp_pkey, temp_pkey, temp_pkey, temp_pkey,
> temp_pkey, temp_pkey, temp_pkey, temp_pkey, temp_pkey, temp_pkey,
> temp_pkey on "temp" t  (cost=0.00..36.49 rows=12 width=8) (actual
> time=0.046..0.137 rows=12 loops=1)
>   Index Cond: ((id = 1) OR (id = 2) OR (id = 3) OR (id = 4) OR (id = 5) OR
> (id = 6) OR (id = 7) OR (id = 8) OR (id = 9) OR (id = 10) OR (id = 11) OR (id
> = 12))
> Total runtime: 0.292 ms
>
> -----------------------------------------------------------------------------------
> explain analyze select * from temp t where id in (select * from
> tools.array_srf('{1,2,3,4,5,6,7,8,9,10,11,12}'));
> Nested Loop  (cost=15.00..620.20 rows=200 width=8) (actual time=0.211..0.368
> rows=12 loops=1)
>   ->  HashAggregate  (cost=15.00..15.00 rows=200 width=4) (actual
> time=0.160..0.173 rows=12 loops=1)
>         ->  Function Scan on array_srf  (cost=0.00..12.50 rows=1000 width=4)
> (actual time=0.127..0.139 rows=12 loops=1)
>   ->  Index Scan using temp_pkey on "temp" t  (cost=0.00..3.01 rows=1
> width=8) (actual time=0.010..0.012 rows=1 loops=12)
>         Index Cond: (t.id = "outer".array_srf)
> Total runtime: 0.494 ms
>
> -----------------------------------------------------------------------------------
> explain analyze select * from temp t, (select * from
> tools.array_srf('{1,2,3,4,5,6,7,8,9,10,11,12}')) foo where foo.array_srf =
> t.id;
>
> Merge Join  (cost=62.33..2824.80 rows=1000 width=12) (actual
> time=0.215..0.286 rows=12 loops=1)
>   Merge Cond: ("outer".id = "inner".array_srf)
>   ->  Index Scan using temp_pkey on "temp" t  (cost=0.00..2419.79
> rows=131072 width=8) (actual time=0.032..0.056 rows=13 loops=1)
>   ->  Sort  (cost=62.33..64.83 rows=1000 width=4) (actual time=0.169..0.173
> rows=12 loops=1)
>         Sort Key: array_srf.array_srf
>         ->  Function Scan on array_srf  (cost=0.00..12.50 rows=1000 width=4)
> (actual time=0.127..0.139 rows=12 loops=1)
> Total runtime: 0.391 ms
>
> -----------------------------------------------------------------------------------
> Note that the meaning is different ; the IN removes duplicates in the array
> but the join does not.
>
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

В списке pgsql-performance по дате отправления:

Предыдущее
От: PFC
Дата:
Сообщение: Re: [SQL] OFFSET impact on Performance???
Следующее
От: "Merlin Moncure"
Дата:
Сообщение: Re: [SQL] OFFSET impact on Performance???