Оптимизация большого запроса возможна? / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация большого запроса возможна?

65 сообщений из 65, показаны все 3 страниц

все

Оптимизация большого запроса возможна?

#35373518

UKY

Гость

Доброго времени суток, многоуважаемые :)

Помогите, пожалуйста, оптимизировать запрос:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.

--explain analyze
select 	servers.ip,
	services.nick,
	folders.path,
	files.name || '.' || files.type,
	files.size,
	tths.added,
	tths.tth,
	tths.id,
	tths.count AS count
from	servers
	join services	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
	join tths	on (files.tth = tths.id)
	join
--explain analyze
	(select distinct on (tths.tth)
		tths.id	AS tth_id
		from	files join tths on files.tth = tths.id
		where	files.size >  0  and
			files.type is not null
			and lower(files.name) like '%'||'панда'||'%'
	) AS inq
	on (tths.id = inq.tth_id)
UNION
--explain analyze
select 	servers.ip,
	services.nick,
	folders.path,
	files.name,
	files.size,
	NULL,
	NULL,
	NULL,
	NULL AS count
from	servers
	join services	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
where
	files.type is null and
	files.tth is null and
	lower(files.name) like '%'||'панда'||'%'
order 	by count desc NULLS LAST
limit	 100 ;

EXPLAIN ANALYZE выдает такое:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.

 Limit  (cost= 486668 . 68 .. 486668 . 93  rows= 100  width= 406 ) (actual time= 23663 . 131 .. 23663 . 277  rows= 29  loops= 1 )
   ->  Sort  (cost= 486668 . 68 .. 486669 . 10  rows= 166  width= 406 ) (actual time= 23663 . 115 .. 23663 . 163  rows= 29  loops= 1 )
         Sort Key: public.tths.count
         Sort Method:  quicksort  Memory: 32kB
         ->  Unique  (cost= 486658 . 41 .. 486662 . 56  rows= 166  width= 406 ) (actual time= 23662 . 787 .. 23662 . 965  rows= 29  loops= 1 )
               ->  Sort  (cost= 486658 . 41 .. 486658 . 83  rows= 166  width= 406 ) (actual time= 23662 . 781 .. 23662 . 834  rows= 29  loops= 1 )
                     Sort Key: public.servers.ip, public.services.nick, public.folders.path, ((((public.files.name)::text || '.'::text) || (public.files.type)::text)), public.files.size, public.tths.added, public.tths.tth, public.tths.id, public.tths.count
                     Sort Method:  quicksort  Memory: 32kB
                     ->  Append  (cost= 180382 . 35 .. 486652 . 29  rows= 166  width= 406 ) (actual time= 7940 . 199 .. 23662 . 361  rows= 29  loops= 1 )
                           ->  Hash Join  (cost= 180382 . 35 .. 349969 . 25  rows= 165  width= 406 ) (actual time= 7940 . 194 .. 22407 . 952  rows= 27  loops= 1 )
                                 Hash Cond: (public.services.server = public.servers.id)
                                 ->  Hash Join  (cost= 180375 . 35 .. 349958 . 75  rows= 165  width= 403 ) (actual time= 7939 . 021 .. 22406 . 235  rows= 27  loops= 1 )
                                       Hash Cond: (public.folders.service = public.services.id)
                                       ->  Nested Loop  (cost= 180367 . 29 .. 349948 . 42  rows= 165  width= 396 ) (actual time= 7937 . 755 .. 22404 . 789  rows= 27  loops= 1 )
                                             ->  Hash Join  (cost= 180367 . 29 .. 348704 . 12  rows= 165  width= 334 ) (actual time= 7937 . 612 .. 22402 . 311  rows= 27  loops= 1 )
                                                   Hash Cond: (public.files.tth = public.tths.id)
                                                   ->  Seq Scan on files  (cost= 0 . 00 .. 156340 . 94  rows= 3198894  width= 286 ) (actual time= 0 . 033 .. 7397 . 845  rows= 3198894  loops= 1 )
                                                   ->  Hash  (cost= 180367 . 27 .. 180367 . 27  rows= 2  width= 56 ) (actual time= 7933 . 336 .. 7933 . 336  rows= 10  loops= 1 )
                                                         ->  Nested Loop  (cost= 180349 . 95 .. 180367 . 27  rows= 2  width= 56 ) (actual time= 7933 . 054 .. 7933 . 301  rows= 10  loops= 1 )
                                                               ->  Unique  (cost= 180349 . 95 .. 180349 . 96  rows= 2  width= 44 ) (actual time= 7933 . 019 .. 7933 . 107  rows= 10  loops= 1 )
                                                                     ->  Sort  (cost= 180349 . 95 .. 180349 . 95  rows= 2  width= 44 ) (actual time= 7933 . 013 .. 7933 . 045  rows= 20  loops= 1 )
                                                                           Sort Key: public.tths.tth
                                                                           Sort Method:  quicksort  Memory: 26kB
                                                                           ->  Nested Loop  (cost= 0 . 00 .. 180349 . 94  rows= 2  width= 44 ) (actual time= 39 . 126 .. 7932 . 723  rows= 20  loops= 1 )
                                                                                 ->  Seq Scan on files  (cost= 0 . 00 .. 180332 . 65  rows= 2  width= 4 ) (actual time= 4 . 062 .. 7825 . 612  rows= 20  loops= 1 )
                                                                                       Filter: ((type IS NOT NULL) AND (size >  0 ) AND (lower((name)::text) ~~ '%панда%'::text))
                                                                                 ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 63  rows= 1  width= 44 ) (actual time= 5 . 337 .. 5 . 341  rows= 1  loops= 20 )
                                                                                       Index Cond: (public.tths.id = public.files.tth)
                                                               ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 63  rows= 1  width= 52 ) (actual time= 0 . 009 .. 0 . 012  rows= 1  loops= 10 )
                                                                     Index Cond: (public.tths.id = public.tths.id)
                                             ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 7 . 53  rows= 1  width= 74 ) (actual time= 0 . 074 .. 0 . 078  rows= 1  loops= 27 )
                                                   Index Cond: (public.folders.id = public.files.path)
                                       ->  Hash  (cost= 5 . 25 .. 5 . 25  rows= 225  width= 15 ) (actual time= 1 . 223 .. 1 . 223  rows= 225  loops= 1 )
                                             ->  Seq Scan on services  (cost= 0 . 00 .. 5 . 25  rows= 225  width= 15 ) (actual time= 0 . 069 .. 0 . 632  rows= 225  loops= 1 )
                                 ->  Hash  (cost= 4 . 22 .. 4 . 22  rows= 222  width= 11 ) (actual time= 1 . 083 .. 1 . 083  rows= 222  loops= 1 )
                                       ->  Seq Scan on servers  (cost= 0 . 00 .. 4 . 22  rows= 222  width= 11 ) (actual time= 0 . 018 .. 0 . 503  rows= 222  loops= 1 )
                           ->  Subquery Scan "*SELECT* 2"  (cost= 4019 . 77 .. 136681 . 39  rows= 1  width= 100 ) (actual time= 153 . 754 .. 1254 . 284  rows= 2  loops= 1 )
                                 ->  Nested Loop  (cost= 4019 . 77 .. 136681 . 38  rows= 1  width= 100 ) (actual time= 153 . 745 .. 1254 . 265  rows= 2  loops= 1 )
                                       ->  Nested Loop  (cost= 4019 . 77 .. 136677 . 08  rows= 1  width= 97 ) (actual time= 153 . 691 .. 1254 . 156  rows= 2  loops= 1 )
                                             ->  Nested Loop  (cost= 4019 . 77 .. 136672 . 13  rows= 1  width= 90 ) (actual time= 153 . 627 .. 1254 . 040  rows= 2  loops= 1 )
                                                   ->  Bitmap Heap Scan on files  (cost= 4019 . 77 .. 136663 . 83  rows= 1  width= 28 ) (actual time= 153 . 589 .. 1253 . 883  rows= 2  loops= 1 )
                                                         Recheck Cond: (type IS NULL)
                                                         Filter: ((tth IS NULL) AND (lower((name)::text) ~~ '%панда%'::text))
                                                         ->  Bitmap Index Scan on files_type_folder_index  (cost= 0 . 00 .. 4019 . 77  rows= 196199  width= 0 ) (actual time= 99 . 075 .. 99 . 075  rows= 199141  loops= 1 )
                                                               Index Cond: (type IS NULL)
                                                   ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 8 . 29  rows= 1  width= 74 ) (actual time= 0 . 058 .. 0 . 060  rows= 1  loops= 2 )
                                                         Index Cond: (public.folders.id = public.files.path)
                                             ->  Index Scan using services_pkey on services  (cost= 0 . 00 .. 4 . 93  rows= 1  width= 15 ) (actual time= 0 . 044 .. 0 . 046  rows= 1  loops= 2 )
                                                   Index Cond: (public.services.id = public.folders.service)
                                       ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 4 . 29  rows= 1  width= 11 ) (actual time= 0 . 038 .. 0 . 040  rows= 1  loops= 2 )
                                             Index Cond: (public.servers.id = public.services.server)
 Total runtime:  23664 . 705  ms
( 52  rows)

Пробовал создавать такие индексы:

Код: plaintext

1.
2.
3.
4.
5.
6.

--CREATE INDEX files_size_index ON files(size NULLS LAST);
--CREATE INDEX tths_count_index ON tths(count DESC NULLS LAST);
--CREATE INDEX tths_tth_index ON tths(tth);
--CREATE INDEX files_lower_name_index ON files(lower(name));
CREATE INDEX files_type_folder_index ON files(type) WHERE type is null;
--CREATE INDEX files_type_no_folder_index ON files(type) WHERE type is not null;

Но использовался только тот индекс, который не закомментирован.

Размерность таблиц:
files 3,2 mln
tths 1,6 mln
folders 0,2 mln
services 235
servers 222

VACUUM ANALYZE делал. Какие еще меры, помимо обновления железа (планируется в будущем) можно предпринять?

Структура таблиц приведена ниже:

Код: plaintext

CREATE TABLE servers
(
	id	serial		PRIMARY KEY,
	ip	inet,
	network	integer,
	name	varchar( 250 ),
	size	bigint,
	added	date		DEFAULT CURRENT_DATE
);

CREATE TABLE services
(
	id	serial		PRIMARY KEY,
	server	integer,
	type	integer,
	nick	varchar( 250 ),
	added	date		DEFAULT CURRENT_DATE,
	last	date,
	descr	varchar( 250 ),
	size	bigint,
	files	integer,
	folders	integer
);
ALTER TABLE services ADD CONSTRAINT fk_services_servers
	FOREIGN KEY (server) REFERENCES servers(id);
ALTER TABLE services ADD CONSTRAINT fk_services_service
	FOREIGN KEY (type) REFERENCES service(id);

CREATE TABLE folders
(
	id	serial		PRIMARY KEY,
	path	varchar( 250 ),
	service	integer,
	rubric	integer		DEFAULT NULL
);
ALTER TABLE folders ADD CONSTRAINT fk_folders_rubrics
	FOREIGN KEY (rubric) REFERENCES rubrics(id);
ALTER TABLE folders ADD CONSTRAINT fk_folders_services
	FOREIGN KEY (service) REFERENCES services(id);

CREATE TABLE tths
(
	id	serial		PRIMARY KEY,
	TTH	character( 39 )	UNIQUE,
	count	integer,
	content	varchar,
	vector	tsvector,
	added	date		DEFAULT CURRENT_DATE
);

CREATE TABLE files
(
	id	serial		PRIMARY KEY,
	path	integer,
	name	varchar( 250 )	NOT NULL,
	type	character( 250 ),
	size	bigint,
	tth	integer
);
ALTER TABLE files ADD CONSTRAINT fk_files_folders
	FOREIGN KEY (path) REFERENCES folders(id);
ALTER TABLE files ADD CONSTRAINT fk_files_tths
	FOREIGN KEY (tth) REFERENCES tths(id);

...

Рейтинг:

0 / 0

15.06.2008, 15:32

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35373602

Гостик

Гость

Возможно поможет кластерный индекс.

http://www.postgresql.org/docs/8.2/static/sql-cluster.html

Код: plaintext

CLUSTER
Name
CLUSTER -- cluster a table according to an index
Synopsis

CLUSTER indexname ON tablename
CLUSTER tablename
CLUSTER

Description

CLUSTER instructs PostgreSQL to cluster the table specified by tablename based on the index specified by indexname. The index must already have been defined on tablename.

When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order. If one wishes, one can periodically recluster by issuing the command again.

When a table is clustered, PostgreSQL remembers on which index it was clustered. The form CLUSTER tablename reclusters the table on the same index that it was clustered before.

CLUSTER without any parameter reclusters all the tables in the current database that the calling user owns, or all tables if called by a superuser. (Never-clustered tables are not included.) This form of CLUSTER cannot be executed inside a transaction block.

When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the CLUSTER is finished.
Parameters

indexname

    The name of an index. 
tablename

    The name (possibly schema-qualified) of a table. 

Notes

CLUSTER loses all visibility information of tuples, which makes the table look empty to any snapshot that was taken before the CLUSTER command finished. That makes CLUSTER unsuitable for applications where transactions that access the table being clustered are run concurrently with CLUSTER. This is most visible with serializable transactions, because they take only one snapshot at the beginning of the transaction, but read-committed transactions are also affected.

In cases where you are accessing single rows randomly within a table, the actual order of the data in the table is unimportant. However, if you tend to access some data more than others, and there is an index that groups them together, you will benefit from using CLUSTER. If you are requesting a range of indexed values from a table, or a single indexed value that has multiple rows that match, CLUSTER will help because once the index identifies the table page for the first row that matches, all other rows that match are probably already on the same table page, and so you save disk accesses and speed up the query.

During the cluster operation, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes.

Because CLUSTER remembers the clustering information, one can cluster the tables one wants clustered manually the first time, and setup a timed event similar to VACUUM so that the tables are periodically reclustered.

Because the planner records statistics about the ordering of tables, it is advisable to run ANALYZE on the newly clustered table. Otherwise, the planner may make poor choices of query plans.

There is another way to cluster data. The CLUSTER command reorders the original table by scanning it using the index you specify. This can be slow on large tables because the rows are fetched from the table in index order, and if the table is disordered, the entries are on random pages, so there is one disk page retrieved for every row moved. (PostgreSQL has a cache, but the majority of a big table will not fit in the cache.) The other way to cluster a table is to use

CREATE TABLE newtable AS
    SELECT * FROM table ORDER BY columnlist;

which uses the PostgreSQL sorting code to produce the desired order; this is usually much faster than an index scan for disordered data. Then you drop the old table, use ALTER TABLE ... RENAME to rename newtable to the old name, and recreate the table's indexes. The big disadvantage of this approach is that it does not preserve OIDs, constraints, foreign key relationships, granted privileges, and other ancillary properties of the table — all such items must be manually recreated. Another disadvantage is that this way requires a sort temporary file about the same size as the table itself, so peak disk usage is about three times the table size instead of twice the table size.
Examples

Cluster the table employees on the basis of its index emp_ind:

CLUSTER emp_ind ON emp;

Cluster the employees table using the same index that was used before:

CLUSTER emp;

Cluster all tables in the database that have previously been clustered:

CLUSTER;

Compatibility

There is no CLUSTER statement in the SQL standard.

...

Рейтинг:

0 / 0

15.06.2008, 20:48

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35374014

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

Код: plaintext

1.
2.
3.

->  Hash Join  (cost= 180367 . 29 .. 348704 . 12  rows= 165  width= 334 ) (actual time= 7937 . 612 .. 22402 . 311  rows= 27  loops= 1 )
      Hash Cond: (public.files.tth = public.tths.id)
      ->  Seq Scan on files  (cost= 0 . 00 .. 156340 . 94  rows= 3198894  width= 286 ) (actual time= 0 . 033 .. 7397 . 845  rows= 3198894  loops= 1 )
      ->  Hash  (cost= 180367 . 27 .. 180367 . 27  rows= 2  width= 56 ) (actual time= 7933 . 336 .. 7933 . 336  rows= 10  loops= 1 )

создать индекс по files(tth)

Код: plaintext

->  Seq Scan on files  (cost= 0 . 00 .. 180332 . 65  rows= 2  width= 4 ) (actual time= 4 . 062 .. 7825 . 612  rows= 20  loops= 1 )
      Filter: ((type IS NOT NULL) AND (size >  0 ) AND (lower((name)::text) ~~ '%панда%'::text))

Код: plaintext

1.
2.
3.
4.

->  Bitmap Heap Scan on files  (cost= 4019 . 77 .. 136663 . 83  rows= 1  width= 28 ) (actual time= 153 . 589 .. 1253 . 883  rows= 2  loops= 1 )
      Recheck Cond: (type IS NULL)
      Filter: ((tth IS NULL) AND (lower((name)::text) ~~ '%панда%'::text))
      ->  Bitmap Index Scan on files_type_folder_index  (cost= 0 . 00 .. 4019 . 77  rows= 196199  width= 0 ) (actual time= 99 . 075 .. 99 . 075  rows= 199141  loops= 1 )
            Index Cond: (type IS NULL)

использовать полнотекстовый поиск

...

Рейтинг:

0 / 0

16.06.2008, 11:00

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35374273

UKY

Гость

2Гостик:
Пытался делать кластерный индекс (ох, как это долго все происходило :) по files(name), но не помогло. Этот индекс не использовался.

2LeXa_NalBat:
А если я пишу

Код: plaintext

ALTER TABLE files ADD CONSTRAINT fk_files_tths FOREIGN KEY (tth) REFERENCES tths(id);

то индекс разве сам не создается? Во всяком случае создал такой индекс, но СУБД не хочет его использовать (EXPLAIN ANALYZE см. ниже).

Прикрутил полнотекстовый поиск, создал для него GIN индекс. Круто. Быстро. Ничего не скажешь :)

Код: plaintext

Sort  (cost= 784418 . 75 .. 785975 . 67  rows= 622765  width= 406 ) (actual time= 21129 . 302 .. 21129 . 382  rows= 47  loops= 1 )
  Sort Key: public.tths.count, public.tths.id
  Sort Method:  quicksort  Memory: 38kB
  ->  Unique  (cost= 593966 . 70 .. 609535 . 83  rows= 622765  width= 406 ) (actual time= 21128 . 882 .. 21129 . 151  rows= 47  loops= 1 )
        ->  Sort  (cost= 593966 . 70 .. 595523 . 61  rows= 622765  width= 406 ) (actual time= 21128 . 877 .. 21128 . 956  rows= 47  loops= 1 )
              Sort Key: public.servers.ip, public.services.nick, public.folders.path, (ts_headline('russian'::regconfig, (((public.files.name)::text || '.'::text) || (public.files.type)::text), to_tsquery('панда'::text))), public.files.size, public.tths.added, public.tths.tth, public.tths.id, public.tths.count
              Sort Method:  quicksort  Memory: 38kB
              ->  Append  (cost= 94892 . 51 .. 419083 . 78  rows= 622765  width= 406 ) (actual time= 5312 . 341 .. 21128 . 448  rows= 47  loops= 1 )
                    ->  Hash Join  (cost= 94892 . 51 .. 408468 . 11  rows= 622753  width= 406 ) (actual time= 5312 . 337 .. 21064 . 884  rows= 45  loops= 1 )
                          Hash Cond: (public.files.path = public.folders.id)
                          ->  Hash Join  (cost= 78007 . 13 .. 311386 . 38  rows= 622753  width= 334 ) (actual time= 2213 . 472 .. 17848 . 099  rows= 45  loops= 1 )
                                Hash Cond: (public.files.tth = public.tths.id)
                                ->  Seq Scan on files  (cost= 0 . 00 .. 202236 . 19  rows= 4142919  width= 286 ) (actual time= 0 . 038 .. 9504 . 883  rows= 4142919  loops= 1 )
                                ->  Hash  (cost= 77960 . 87 .. 77960 . 87  rows= 3701  width= 56 ) (actual time= 1 . 504 .. 1 . 504  rows= 15  loops= 1 )
                                      ->  Nested Loop  (cost= 47210 . 25 .. 77960 . 87  rows= 3701  width= 56 ) (actual time= 1 . 102 .. 1 . 462  rows= 15  loops= 1 )
                                            ->  Unique  (cost= 47210 . 25 .. 47228 . 75  rows= 3701  width= 44 ) (actual time= 1 . 087 .. 1 . 236  rows= 15  loops= 1 )
                                                  ->  Sort  (cost= 47210 . 25 .. 47219 . 50  rows= 3701  width= 44 ) (actual time= 1 . 082 .. 1 . 135  rows= 35  loops= 1 )
                                                        Sort Key: public.tths.tth
                                                        Sort Method:  quicksort  Memory: 27kB
                                                        ->  Nested Loop  (cost= 125 . 22 .. 46990 . 90  rows= 3701  width= 44 ) (actual time= 0 . 152 .. 0 . 785  rows= 35  loops= 1 )
                                                              ->  Bitmap Heap Scan on files  (cost= 125 . 22 .. 14614 . 53  rows= 3910  width= 4 ) (actual time= 0 . 122 .. 0 . 222  rows= 35  loops= 1 )
                                                                    Recheck Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                                                                    Filter: ((type IS NOT NULL) AND (size >  0 ))
                                                                    ->  Bitmap Index Scan on files_name_russian_vector_index  (cost= 0 . 00 .. 124 . 24  rows= 4143  width= 0 ) (actual time= 0 . 106 .. 0 . 106  rows= 37  loops= 1 )
                                                                          Index Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                                                              ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 44 ) (actual time= 0 . 007 .. 0 . 009  rows= 1  loops= 35 )
                                                                    Index Cond: (public.tths.id = public.files.tth)
                                            ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 28  rows= 1  width= 52 ) (actual time= 0 . 006 .. 0 . 008  rows= 1  loops= 15 )
                                                  Index Cond: (public.tths.id = public.tths.id)
                          ->  Hash  (cost= 9871 . 73 .. 9871 . 73  rows= 267972  width= 84 ) (actual time= 3098 . 566 .. 3098 . 566  rows= 267972  loops= 1 )
                                ->  Hash Join  (cost= 26 . 39 .. 9871 . 73  rows= 267972  width= 84 ) (actual time= 7 . 002 .. 2080 . 252  rows= 267972  loops= 1 )
                                      Hash Cond: (public.folders.service = public.services.id)
                                      ->  Seq Scan on folders  (cost= 0 . 00 .. 6160 . 72  rows= 267972  width= 74 ) (actual time= 0 . 074 .. 653 . 960  rows= 267972  loops= 1 )
                                      ->  Hash  (cost= 22 . 46 .. 22 . 46  rows= 315  width= 18 ) (actual time= 6 . 883 .. 6 . 883  rows= 315  loops= 1 )
                                            ->  Hash Join  (cost= 9 . 97 .. 22 . 46  rows= 315  width= 18 ) (actual time= 1 . 538 .. 6 . 103  rows= 315  loops= 1 )
                                                  Hash Cond: (public.services.server = public.servers.id)
                                                  ->  Seq Scan on services  (cost= 0 . 00 .. 8 . 15  rows= 315  width= 15 ) (actual time= 0 . 011 .. 0 . 661  rows= 315  loops= 1 )
                                                  ->  Hash  (cost= 6 . 10 .. 6 . 10  rows= 310  width= 11 ) (actual time= 1 . 494 .. 1 . 494  rows= 310  loops= 1 )
                                                        ->  Seq Scan on servers  (cost= 0 . 00 .. 6 . 10  rows= 310  width= 11 ) (actual time= 0 . 013 .. 0 . 713  rows= 310  loops= 1 )
                    ->  Subquery Scan *SELECT*  2   (cost= 4240 . 01 .. 4388 . 14  rows= 12  width= 84 ) (actual time= 63 . 301 .. 63 . 388  rows= 2  loops= 1 )
                          ->  Hash Join  (cost= 4240 . 01 .. 4388 . 02  rows= 12  width= 84 ) (actual time= 63 . 292 .. 63 . 369  rows= 2  loops= 1 )
                                Hash Cond: (public.services.server = public.servers.id)
                                ->  Hash Join  (cost= 4230 . 04 .. 4377 . 82  rows= 12  width= 81 ) (actual time= 61 . 543 .. 61 . 585  rows= 2  loops= 1 )
                                      Hash Cond: (public.folders.service = public.services.id)
                                      ->  Nested Loop  (cost= 4217 . 95 .. 4365 . 56  rows= 12  width= 74 ) (actual time= 60 . 215 .. 60 . 249  rows= 2  loops= 1 )
                                            ->  Bitmap Heap Scan on files  (cost= 4217 . 95 .. 4265 . 85  rows= 12  width= 12 ) (actual time= 60 . 171 .. 60 . 175  rows= 2  loops= 1 )
                                                  Recheck Cond: ((to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text)) AND (tth IS NULL) AND (type IS NULL))
                                                  ->  BitmapAnd  (cost= 4217 . 95 .. 4217 . 95  rows= 12  width= 0 ) (actual time= 60 . 159 .. 60 . 159  rows= 0  loops= 1 )
                                                        ->  Bitmap Index Scan on files_name_russian_vector_index  (cost= 0 . 00 .. 124 . 24  rows= 4143  width= 0 ) (actual time= 0 . 069 .. 0 . 069  rows= 37  loops= 1 )
                                                              Index Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                                                        ->  Bitmap Index Scan on files_tth_index  (cost= 0 . 00 .. 4093 . 45  rows= 11784  width= 0 ) (actual time= 60 . 064 .. 60 . 064  rows= 267972  loops= 1 )
                                                              Index Cond: (tth IS NULL)
                                            ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 8 . 30  rows= 1  width= 74 ) (actual time= 0 . 018 .. 0 . 021  rows= 1  loops= 2 )
                                                  Index Cond: (public.folders.id = public.files.path)
                                      ->  Hash  (cost= 8 . 15 .. 8 . 15  rows= 315  width= 15 ) (actual time= 1 . 304 .. 1 . 304  rows= 315  loops= 1 )
                                            ->  Seq Scan on services  (cost= 0 . 00 .. 8 . 15  rows= 315  width= 15 ) (actual time= 0 . 011 .. 0 . 616  rows= 315  loops= 1 )
                                ->  Hash  (cost= 6 . 10 .. 6 . 10  rows= 310  width= 11 ) (actual time= 1 . 632 .. 1 . 632  rows= 310  loops= 1 )
                                      ->  Seq Scan on servers  (cost= 0 . 00 .. 6 . 10  rows= 310  width= 11 ) (actual time= 0 . 020 .. 0 . 595  rows= 310  loops= 1 )
Total runtime:  21129 . 891  ms

Вот только хотелось бы найти именно то слово, которое было задано в запросе. Т.е. если спросили "панда", то должно вернуться "Панда", "Пандамима", "Недопандас" и все в этом роде, но не "панды", "пандой"...
Это можно сделать при помощи полнотекстового поиска? В мануале чего-то такого не нашел...

...

Рейтинг:

0 / 0

16.06.2008, 12:43

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35374471

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKYА если я пишу

Код: plaintext

ALTER TABLE files ADD CONSTRAINT fk_files_tths FOREIGN KEY (tth) REFERENCES tths(id);

то индекс разве сам не создается?не создается

UKYВо всяком случае создал такой индекс, но СУБД не хочет его использовать (EXPLAIN ANALYZE см. ниже).

Код: plaintext

1.
2.
3.
4.

->  Bitmap Heap Scan on files  (cost= 125 . 22 .. 14614 . 53  rows= 3910  width= 4 ) (actual time= 0 . 122 .. 0 . 222  rows= 35  loops= 1 )
  Recheck Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
  Filter: ((type IS NOT NULL) AND (size >  0 ))
  ->  Bitmap Index Scan on files_name_russian_vector_index  (cost= 0 . 00 .. 124 . 24  rows= 4143  width= 0 ) (actual time= 0 . 106 .. 0 . 106  rows= 37  loops= 1 )
    Index Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))

думаю, что постгрес не использует при джоине этот индекс из-за того, что ошибается на два порядка в оценке кол-ва строк, удовлетворяющих условию полнотекстового поиска (rows=4143, actual rows=37). кажется, постгрес не умеет оценивать объем выборки по полнотекстовому индексу: тынц . можно попробовать запинать командами set enable_* to off|on, для начала set enable_hashjoin to off.

UKY

Код: plaintext

1.
2.
3.

->  Hash Join  (cost= 26 . 39 .. 9871 . 73  rows= 267972  width= 84 ) (actual time= 7 . 002 .. 2080 . 252  rows= 267972  loops= 1 )
  Hash Cond: (public.folders.service = public.services.id)
  ->  Seq Scan on folders  (cost= 0 . 00 .. 6160 . 72  rows= 267972  width= 74 ) (actual time= 0 . 074 .. 653 . 960  rows= 267972  loops= 1 )
  ->  Hash  (cost= 22 . 46 .. 22 . 46  rows= 315  width= 18 ) (actual time= 6 . 883 .. 6 . 883  rows= 315  loops= 1 )

создайте индекс по folders(service)

UKYВот только хотелось бы найти именно то слово, которое было задано в запросе. Т.е. если спросили "панда", то должно вернуться "Панда", "Пандамима", "Недопандас" и все в этом роде, но не "панды", "пандой"...
Это можно сделать при помощи полнотекстового поиска? В мануале чего-то такого не нашел...я с полнотекстовым поиском не работал. может быть можно в качестве разделителя "слов" считать "границу букв"? тогда каждую букву полнотекстовый поиск будет считать отдельным словом, и получите нужны вам поиск.

...

Рейтинг:

0 / 0

16.06.2008, 13:53

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35377393

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

"постгрес не умеет оценивать объем выборки по полнотекстовому индексу"

может обернуть полнотекстовый поиск по files.name в функцию на языке sql, для которой при создании указать ROWS например равным 10, также можно указать COST.

...

Рейтинг:

0 / 0

17.06.2008, 15:03

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35381282

UKY

Гость

Попробовал

Код: plaintext

set enable_hashjoin to off

стало хуже :)

LeXa NalBatя с полнотекстовым поиском не работал. может быть можно в качестве разделителя "слов" считать "границу букв"? тогда каждую букву полнотекстовый поиск будет считать отдельным словом, и получите нужны вам поиск.
Это сделать реально, но там тогда придется не каждую букву, а все части слова выделять, а это ох сколько памяти и времени на постоение индексов занимать будет...
Пока думаю сделать полнотекстовый поиск и уже результаты выборки доколнительно проверять LIKE '%панда%'. Хотя тоже часть результатов теряться будет...

LeXa NalBat"постгрес не умеет оценивать объем выборки по полнотекстовому индексу"

может обернуть полнотекстовый поиск по files.name в функцию на языке sql, для которой при создании указать ROWS например равным 10, также можно указать COST.

Тут я уже Вас не понимаю :) Еще ни разу не писал функции на постгресе. Может быть поэтому...

Огромное Вам спасибо за помощь!

...

Рейтинг:

0 / 0

18.06.2008, 19:55

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35381542

Гость

UKY
Вот только хотелось бы найти именно то слово, которое было задано в запросе. Т.е. если спросили "панда", то должно вернуться "Панда", "Пандамима", "Недопандас" и все в этом роде, но не "панды", "пандой"...
Это можно сделать при помощи полнотекстового поиска? В мануале чего-то такого не нашел...

в 8.3 и младше нельзя, в 8.4 будет можно (уже в CVS даже есть)

...

Рейтинг:

0 / 0

19.06.2008, 02:21

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35382358

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKY LeXa NalBatможет обернуть полнотекстовый поиск по files.name в функцию на языке sql, для которой при создании указать ROWS например равным 10, также можно указать COST.Тут я уже Вас не понимаю :) Еще ни разу не писал функции на постгресе. Может быть поэтому...думаю, что это ухищрение может ускорить запрос. сделать что-то типа

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

create table files ( id integer, name text );
insert into files select * from generate_series(1,1000000);
create index files_name_russian_vector_index on files using gin(to_tsvector('russian', name));
analyze files;
create function files_name_full_text_search(text) returns setof files language 'sql'  rows 10  as
  'select * from files where to_tsvector(''russian'', name) @@ to_tsquery($1)';
explain select * from files where to_tsvector('russian', name) @@ to_tsquery('панда');
explain select * from files_name_full_text_search('панда');
drop function files_name_full_text_search(text);
drop table files;

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on files  (cost=12.01..2409.87  rows=1000  width=36)
   Recheck Cond: (to_tsvector('russian'::regconfig, name) @@ to_tsquery('панда'::text))
   ->  Bitmap Index Scan on files_name_russian_vector_index  (cost=0.00..11.76 rows=1000 width=0)
         Index Cond: (to_tsvector('russian'::regconfig, name) @@ to_tsquery('панда'::text))
(4 rows)
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Function Scan on files_name_full_text_search  (cost=0.00..2.60  rows=10  width=36)
(1 row)

...

Рейтинг:

0 / 0

19.06.2008, 12:31

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35383823

UKY

Гость

iz UKY
Вот только хотелось бы найти именно то слово, которое было задано в запросе. Т.е. если спросили "панда", то должно вернуться "Панда", "Пандамима", "Недопандас" и все в этом роде, но не "панды", "пандой"...
Это можно сделать при помощи полнотекстового поиска? В мануале чего-то такого не нашел...

в 8.3 и младше нельзя, в 8.4 будет можно (уже в CVS даже есть)
Круто :) Надо будет как-нибудь попробовать этот патчик прикрутить...

2 LeXa NalBat:
Это ухищрение не сработало :) Постгрес не обманишь...

...

Рейтинг:

0 / 0

19.06.2008, 20:40

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35384058

stopor

Гость

До выхода версии 8.4 можно сделать поиск по триграммам - контриб pg_trgm. Он шустро работает, правда UTF-8 не понимает.

Принцип такой:
1) Сделать таблицу для хранения отдельных слов

Код: plaintext

CREATE TABLE words (parent_id int, word text);
CREATE INDEX i_words_trgm ON words USING gin (t gin_trgm_ops);

Если слова часто повторяются, то можно разбить на две таблицы: words(word_id, word)-уникальные слова, words_link(word_id,parent_id) -связь слова с исходным текстом.

2) Исходный текст разбить по словам. Можно с помощью ts_vector+ts_stat как в мануале, можно регуляркой

Код: plaintext

1.
2.
3.

INSERT INTO words 
SELECT id, w
FROM (SELECT id, regexp_split_to_table(textfield, E'\\W') as w FROM mydata) a
WHERE w > ''

3) Установить в сессии низкий порог поиска функцией set_limit(), в районе 0.1-0.2. Чем меньше порог, тем больше будет ложных совпадений и медленнее поиск, но зато и нужных слов попадет больше.

4) Поиск должен идти сначала оператором % , а потом из уже ограниченного набора строк надо отсечь мусор обычным like

Код: plaintext

1.
2.
3.
4.

SELECT *
FROM words
WHERE word % 'раствор'
  and word like '%раствор%'

У меня на базе из 50 тысяч слов такой запрос с set_limit(0.2) отрабатывает ~7мс. Чем длиннее искомое слово, тем медленнее, но в 20мс на кэшированнных данных стабильно укладывается.

...

Рейтинг:

0 / 0

20.06.2008, 05:38

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35384360

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKY2 LeXa NalBat:
Это ухищрение не сработало :) Постгрес не обманишь...покажите explain analyze

...

Рейтинг:

0 / 0

20.06.2008, 10:33

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35386409

UKY

Гость

Оказывается, запрос по словам и не тормозит вовсе :)

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

Unique  (cost= 60363 . 09 .. 60397 . 79  rows= 4627  width= 60 ) (actual time= 2 . 442 .. 2 . 705  rows= 24  loops= 1 )
  ->  Sort  (cost= 60363 . 09 .. 60374 . 66  rows= 4627  width= 60 ) (actual time= 2 . 436 .. 2 . 525  rows= 46  loops= 1 )
        Sort Key: tths.tth, files.name
        Sort Method:  quicksort  Memory: 31kB
        ->  Nested Loop  (cost= 158 . 34 .. 60081 . 40  rows= 4627  width= 60 ) (actual time= 0 . 184 .. 1 . 305  rows= 46  loops= 1 )
              ->  Bitmap Heap Scan on files  (cost= 158 . 34 .. 18685 . 88  rows= 4940  width= 20 ) (actual time= 0 . 152 .. 0 . 351  rows= 46  loops= 1 )
                    Recheck Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                    Filter: ((type IS NOT NULL) AND (size >  0 ))
                    ->  Bitmap Index Scan on files_name_russian_vector_index  (cost= 0 . 00 .. 157 . 11  rows= 5294  width= 0 ) (actual time= 0 . 128 .. 0 . 128  rows= 48  loops= 1 )
                          Index Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
              ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 37  rows= 1  width= 44 ) (actual time= 0 . 010 .. 0 . 012  rows= 1  loops= 46 )
                    Index Cond: (tths.id = files.tth)
Total runtime:  2 . 886  ms

Все тормоза уходят в join. На построение всяких хэш таблиц и всего такого:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.

--explain analyze
select 	distinct on (count, tths.id, files.name)
	servers.ip,
	services.nick,
	folders.path,
	ts_headline('russian', files.name || '.' || files.type, to_tsquery('панда')),
	files.size,
	tths.added,
	tths.tth,
	tths.id AS id,
	tths.count AS count
from	servers
	join services	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
	join tths	on (files.tth = tths.id)
	join
--explain analyze
	(select distinct on (tths.tth, files.name)
		tths.id	AS tth_id,
		files.name AS name
		from	files join tths on files.tth = tths.id
		where	files.size >  0  and
			files.type is not null and
			to_tsvector('russian', files.name) @@ to_tsquery('панда')
	) AS inq
	on (tths.id = inq.tth_id)
UNION
--explain analyze
select 	servers.ip,
	services.nick,
	ts_headline('russian', folders.path, to_tsquery('панда')),
	NULL,
	files.size,
	NULL,
	NULL,
	NULL AS id,
	NULL AS count
from	servers
	join services	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
where
	files.type is null and
	files.tth is null and
	to_tsvector('russian', files.name) @@ to_tsquery('панда')
order 	by count desc NULLS LAST, id;

Код: plaintext

Sort  (cost= 1417225 . 78 .. 1419631 . 87  rows= 962436  width= 408 ) (actual time= 57774 . 369 .. 57774 . 414  rows= 30  loops= 1 )
  Sort Key: *SELECT*  1 .count, *SELECT*  1 .id
  Sort Method:  quicksort  Memory: 33kB
  ->  Unique  (cost= 1119877 . 39 .. 1143938 . 29  rows= 962436  width= 408 ) (actual time= 57774 . 094 .. 57774 . 266  rows= 30  loops= 1 )
        ->  Sort  (cost= 1119877 . 39 .. 1122283 . 48  rows= 962436  width= 408 ) (actual time= 57774 . 090 .. 57774 . 140  rows= 30  loops= 1 )
              Sort Key: *SELECT*  1 .ip, *SELECT*  1 .nick, *SELECT*  1 .path, *SELECT*  1 .ts_headline, *SELECT*  1 .size, *SELECT*  1 .added, *SELECT*  1 .tth, *SELECT*  1 .id, *SELECT*  1 .count
              Sort Method:  quicksort  Memory: 33kB
              ->  Append  (cost= 820342 . 82 .. 846589 . 91  rows= 962436  width= 408 ) (actual time= 57682 . 535 .. 57773 . 923  rows= 30  loops= 1 )
                    ->  Subquery Scan *SELECT*  1   (cost= 820342 . 82 .. 839591 . 12  rows= 962415  width= 408 ) (actual time= 57682 . 531 .. 57683 . 179  rows= 28  loops= 1 )
                          ->  Unique  (cost= 820342 . 82 .. 829966 . 97  rows= 962415  width= 408 ) (actual time= 57682 . 525 .. 57683 . 076  rows= 28  loops= 1 )
                                ->  Sort  (cost= 820342 . 82 .. 822748 . 86  rows= 962415  width= 408 ) (actual time= 57682 . 520 .. 57682 . 742  rows= 130  loops= 1 )
                                      Sort Key: public.tths.count, public.tths.id, public.files.name
                                      Sort Method:  quicksort  Memory: 62kB
                                      ->  Hash Join  (cost= 121011 . 44 .. 547061 . 08  rows= 962415  width= 408 ) (actual time= 5438 . 197 .. 57680 . 783  rows= 130  loops= 1 )
                                            Hash Cond: (public.files.path = public.folders.id)
                                            ->  Hash Join  (cost= 99324 . 88 .. 406040 . 05  rows= 962415  width= 334 ) (actual time= 3 . 126 .. 53499 . 275  rows= 130  loops= 1 )
                                                  Hash Cond: (public.files.tth = public.tths.id)
                                                  ->  Seq Scan on files  (cost= 0 . 00 .. 260301 . 67  rows= 5293967  width= 286 ) (actual time= 0 . 035 .. 40929 . 466  rows= 5293967  loops= 1 )
                                                  ->  Hash  (cost= 99267 . 04 .. 99267 . 04  rows= 4627  width= 56 ) (actual time= 2 . 277 .. 2 . 277  rows= 24  loops= 1 )
                                                        ->  Nested Loop  (cost= 60363 . 09 .. 99267 . 04  rows= 4627  width= 56 ) (actual time= 1 . 659 .. 2 . 220  rows= 24  loops= 1 )
                                                              ->  Unique  (cost= 60363 . 09 .. 60397 . 79  rows= 4627  width= 60 ) (actual time= 1 . 644 .. 1 . 861  rows= 24  loops= 1 )
                                                                    ->  Sort  (cost= 60363 . 09 .. 60374 . 66  rows= 4627  width= 60 ) (actual time= 1 . 640 .. 1 . 722  rows= 46  loops= 1 )
                                                                          Sort Key: public.tths.tth, public.files.name
                                                                          Sort Method:  quicksort  Memory: 31kB
                                                                          ->  Nested Loop  (cost= 158 . 34 .. 60081 . 40  rows= 4627  width= 60 ) (actual time= 0 . 153 .. 0 . 986  rows= 46  loops= 1 )
                                                                                ->  Bitmap Heap Scan on files  (cost= 158 . 34 .. 18685 . 88  rows= 4940  width= 20 ) (actual time= 0 . 125 .. 0 . 255  rows= 46  loops= 1 )
                                                                                      Recheck Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                                                                                      Filter: ((type IS NOT NULL) AND (size >  0 ))
                                                                                      ->  Bitmap Index Scan on files_name_russian_vector_index  (cost= 0 . 00 .. 157 . 11  rows= 5294  width= 0 ) (actual time= 0 . 107 .. 0 . 107  rows= 48  loops= 1 )
                                                                                            Index Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                                                                                ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 37  rows= 1  width= 44 ) (actual time= 0 . 007 .. 0 . 009  rows= 1  loops= 46 )
                                                                                      Index Cond: (public.tths.id = public.files.tth)
                                                              ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 38  rows= 1  width= 52 ) (actual time= 0 . 005 .. 0 . 007  rows= 1  loops= 24 )
                                                                    Index Cond: (public.tths.id = public.tths.id)
                                            ->  Hash  (cost= 12744 . 96 .. 12744 . 96  rows= 341648  width= 86 ) (actual time= 3845 . 669 .. 3845 . 669  rows= 342931  loops= 1 )
                                                  ->  Hash Join  (cost= 32 . 82 .. 12744 . 96  rows= 341648  width= 86 ) (actual time= 29 . 308 .. 2802 . 386  rows= 342931  loops= 1 )
                                                        Hash Cond: (public.folders.service = public.services.id)
                                                        ->  Seq Scan on folders  (cost= 0 . 00 .. 8014 . 48  rows= 341648  width= 76 ) (actual time= 20 . 784 .. 1132 . 448  rows= 342931  loops= 1 )
                                                        ->  Hash  (cost= 27 . 69 .. 27 . 69  rows= 410  width= 18 ) (actual time= 8 . 456 .. 8 . 456  rows= 410  loops= 1 )
                                                              ->  Hash Join  (cost= 12 . 96 .. 27 . 69  rows= 410  width= 18 ) (actual time= 2 . 072 .. 7 . 211  rows= 410  loops= 1 )
                                                                    Hash Cond: (public.services.server = public.servers.id)
                                                                    ->  Seq Scan on services  (cost= 0 . 00 .. 9 . 10  rows= 410  width= 15 ) (actual time= 0 . 015 .. 0 . 874  rows= 410  loops= 1 )
                                                                    ->  Hash  (cost= 7 . 98 .. 7 . 98  rows= 398  width= 11 ) (actual time= 2 . 015 .. 2 . 015  rows= 398  loops= 1 )
                                                                          ->  Seq Scan on servers  (cost= 0 . 00 .. 7 . 98  rows= 398  width= 11 ) (actual time= 0 . 014 .. 0 . 960  rows= 398  loops= 1 )
                    ->  Subquery Scan *SELECT*  2   (cost= 6739 . 48 .. 6998 . 79  rows= 21  width= 86 ) (actual time= 90 . 483 .. 90 . 570  rows= 2  loops= 1 )
                          ->  Hash Join  (cost= 6739 . 48 .. 6998 . 58  rows= 21  width= 86 ) (actual time= 90 . 474 .. 90 . 553  rows= 2  loops= 1 )
                                Hash Cond: (public.services.server = public.servers.id)
                                ->  Hash Join  (cost= 6726 . 52 .. 6985 . 23  rows= 21  width= 83 ) (actual time= 88 . 354 .. 88 . 395  rows= 2  loops= 1 )
                                      Hash Cond: (public.folders.service = public.services.id)
                                      ->  Nested Loop  (cost= 6712 . 30 .. 6970 . 72  rows= 21  width= 76 ) (actual time= 86 . 714 .. 86 . 747  rows= 2  loops= 1 )
                                            ->  Bitmap Heap Scan on files  (cost= 6712 . 30 .. 6796 . 03  rows= 21  width= 12 ) (actual time= 86 . 672 .. 86 . 677  rows= 2  loops= 1 )
                                                  Recheck Cond: ((to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text)) AND (tth IS NULL) AND (type IS NULL))
                                                  ->  BitmapAnd  (cost= 6712 . 30 .. 6712 . 30  rows= 21  width= 0 ) (actual time= 86 . 659 .. 86 . 659  rows= 0  loops= 1 )
                                                        ->  Bitmap Index Scan on files_name_russian_vector_index  (cost= 0 . 00 .. 157 . 11  rows= 5294  width= 0 ) (actual time= 0 . 080 .. 0 . 080  rows= 48  loops= 1 )
                                                              Index Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                                                        ->  Bitmap Index Scan on files_tth_index  (cost= 0 . 00 .. 6554 . 93  rows= 21235  width= 0 ) (actual time= 86 . 547 .. 86 . 547  rows= 346939  loops= 1 )
                                                              Index Cond: (tth IS NULL)
                                            ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 8 . 31  rows= 1  width= 76 ) (actual time= 0 . 018 .. 0 . 020  rows= 1  loops= 2 )
                                                  Index Cond: (public.folders.id = public.files.path)
                                      ->  Hash  (cost= 9 . 10 .. 9 . 10  rows= 410  width= 15 ) (actual time= 1 . 617 .. 1 . 617  rows= 410  loops= 1 )
                                            ->  Seq Scan on services  (cost= 0 . 00 .. 9 . 10  rows= 410  width= 15 ) (actual time= 0 . 010 .. 0 . 788  rows= 410  loops= 1 )
                                ->  Hash  (cost= 7 . 98 .. 7 . 98  rows= 398  width= 11 ) (actual time= 1 . 528 .. 1 . 528  rows= 398  loops= 1 )
                                      ->  Seq Scan on servers  (cost= 0 . 00 .. 7 . 98  rows= 398  width= 11 ) (actual time= 0 . 020 .. 0 . 764  rows= 398  loops= 1 )
Total runtime:  57774 . 932  ms

А джоины кроме как использования денормализации можно ускорять? Или их можно как-нибудь убрать?
Еще раз пробовал "set enable_hashjoin to off". Запрос выполнялся 250 секунд и использовался "Merge Join".

P.S.: В понедельник уезжаю на военные сборы. Так что полтора месяца не буду вас всех доставать :)
P.P.S.: Огромное всем спасибо за предложенные варианты ускорения запроса. Сам бы до такого никогда бы не додумался :)

...

Рейтинг:

0 / 0

21.06.2008, 14:27

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35387802

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKYВсе тормоза уходят в join. На построение всяких хэш таблиц и всего такогода. но для этого запроса возможен быстрый план. тормоза происходят из-за того, что постгрес выбирает неоптимальный порядок (или тип) джоинов. причина этой ошибки в том, что он неправильно оценивает кол-во строк удовлетворяющих условию полнотекстового поиска. попробуйте обернуть полнотекстовый поиск в функцию, созданную с опцией ROWS 10, как я писал, это может помочь. покажите explain analyze с использованием функции-обертки.

...

Рейтинг:

0 / 0

23.06.2008, 11:04

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35452659

UKY

Гость

Вернулся со сборов. Жив и здоров :)

LeXa NalBat UKYВсе тормоза уходят в join. На построение всяких хэш таблиц и всего такогода. но для этого запроса возможен быстрый план. тормоза происходят из-за того, что постгрес выбирает неоптимальный порядок (или тип) джоинов. причина этой ошибки в том, что он неправильно оценивает кол-во строк удовлетворяющих условию полнотекстового поиска. попробуйте обернуть полнотекстовый поиск в функцию, созданную с опцией ROWS 10, как я писал, это может помочь. покажите explain analyze с использованием функции-обертки.

Сделал так, как Вы мне порекомендовали:

Код: plaintext

create or replace function tth_search(query text) returns setof record as
$$
BEGIN
	return query
	select distinct on (tths.tth, files.name)
		tths.id,
		files.name
	from	files join tths on files.tth = tths.id
	where	files.size >  0  and
		files.type is not null and
		to_tsvector('russian', files.name) @@ to_tsquery(query);
END;
$$
language plpgsql rows  10 ;

explain analyze
select 	distinct on (count, tths.id, files.name)
	servers.ip,
	services.nick,
	folders.path,
	ts_headline('russian', files.name || '.' || files.type, to_tsquery('панда')),
	files.size,
	tths.added,
	tths.tth,
	tths.id AS id,
	tths.count AS count
from	servers
	join services	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
	join tths	on (files.tth = tths.id)
	join
--explain analyze
	(
select * from tth_search('панда') AS (tth_id int, name varchar)
	) AS inq
	on (tths.id = inq.tth_id)
UNION
--explain analyze
select 	servers.ip,
	services.nick,
	ts_headline('russian', folders.path, to_tsquery('панда')),
	NULL,
	files.size,
	NULL,
	NULL,
	NULL AS id,
	NULL AS count
from	servers
	join services	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
where
	files.type is null and
	files.tth is null and
	to_tsvector('russian', files.name) @@ to_tsquery('панда')
order 	by count desc NULLS LAST, id;

Получил это:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.

Sort  (cost= 304038 . 58 .. 304043 . 83  rows= 2101  width= 408 ) (actual time= 57301 . 893 .. 57301 . 942  rows= 30  loops= 1 )
  Sort Key: "*SELECT* 1".count, "*SELECT* 1".id
  Sort Method:  quicksort  Memory: 33kB
  ->  Unique  (cost= 303870 . 11 .. 303922 . 64  rows= 2101  width= 408 ) (actual time= 57301 . 615 .. 57301 . 783  rows= 30  loops= 1 )
        ->  Sort  (cost= 303870 . 11 .. 303875 . 37  rows= 2101  width= 408 ) (actual time= 57301 . 610 .. 57301 . 657  rows= 30  loops= 1 )
              Sort Key: "*SELECT* 1".ip, "*SELECT* 1".nick, "*SELECT* 1".path, "*SELECT* 1".ts_headline, "*SELECT* 1".size, "*SELECT* 1".added, "*SELECT* 1".tth, "*SELECT* 1".id, "*SELECT* 1".count
              Sort Method:  quicksort  Memory: 33kB
              ->  Append  (cost= 296713 . 78 .. 303754 . 17  rows= 2101  width= 408 ) (actual time= 57210 . 019 .. 57301 . 431  rows= 30  loops= 1 )
                    ->  Subquery Scan "*SELECT* 1"  (cost= 296713 . 78 .. 296755 . 38  rows= 2080  width= 408 ) (actual time= 57210 . 015 .. 57210 . 652  rows= 28  loops= 1 )
                          ->  Unique  (cost= 296713 . 78 .. 296734 . 58  rows= 2080  width= 408 ) (actual time= 57210 . 008 .. 57210 . 536  rows= 28  loops= 1 )
                                ->  Sort  (cost= 296713 . 78 .. 296718 . 98  rows= 2080  width= 408 ) (actual time= 57210 . 003 .. 57210 . 207  rows= 130  loops= 1 )
                                      Sort Key: tths.count, tths.id, public.files.name
                                      Sort Method:  quicksort  Memory: 62kB
                                      ->  Hash Join  (cost= 117 . 72 .. 296599 . 15  rows= 2080  width= 408 ) (actual time= 648 . 109 .. 57208 . 194  rows= 130  loops= 1 )
                                            Hash Cond: (public.services.server = public.servers.id)
                                            ->  Hash Join  (cost= 104 . 76 .. 296531 . 60  rows= 2080  width= 405 ) (actual time= 646 . 112 .. 57198 . 953  rows= 130  loops= 1 )
                                                  Hash Cond: (public.folders.service = public.services.id)
                                                  ->  Nested Loop  (cost= 90 . 54 .. 296488 . 77  rows= 2080  width= 398 ) (actual time= 644 . 049 .. 57196 . 273  rows= 130  loops= 1 )
                                                        ->  Hash Join  (cost= 90 . 54 .. 280244 . 78  rows= 2080  width= 334 ) (actual time= 644 . 000 .. 57193 . 141  rows= 130  loops= 1 )
                                                              Hash Cond: (public.files.tth = tths.id)
                                                              ->  Seq Scan on files  (cost= 0 . 00 .. 260301 . 67  rows= 5293967  width= 286 ) (actual time= 0 . 007 .. 44122 . 366  rows= 5293967  loops= 1 )
                                                              ->  Hash  (cost= 90 . 41 .. 90 . 41  rows= 10  width= 56 ) (actual time= 3 . 629 .. 3 . 629  rows= 24  loops= 1 )
                                                                    ->  Nested Loop  (cost= 0 . 00 .. 90 . 41  rows= 10  width= 56 ) (actual time= 3 . 027 .. 3 . 553  rows= 24  loops= 1 )
                                                                          ->  Function Scan on tth_search  (cost= 0 . 00 .. 2 . 60  rows= 10  width= 4 ) (actual time= 3 . 003 .. 3 . 052  rows= 24  loops= 1 )
                                                                          ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 77  rows= 1  width= 52 ) (actual time= 0 . 009 .. 0 . 012  rows= 1  loops= 24 )
                                                                                Index Cond: (tths.id = tth_search.tth_id)
                                                        ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 7 . 80  rows= 1  width= 76 ) (actual time= 0 . 013 .. 0 . 015  rows= 1  loops= 130 )
                                                              Index Cond: (public.folders.id = public.files.path)
                                                  ->  Hash  (cost= 9 . 10 .. 9 . 10  rows= 410  width= 15 ) (actual time= 2 . 040 .. 2 . 040  rows= 410  loops= 1 )
                                                        ->  Seq Scan on services  (cost= 0 . 00 .. 9 . 10  rows= 410  width= 15 ) (actual time= 0 . 011 .. 0 . 967  rows= 410  loops= 1 )
                                            ->  Hash  (cost= 7 . 98 .. 7 . 98  rows= 398  width= 11 ) (actual time= 1 . 861 .. 1 . 861  rows= 398  loops= 1 )
                                                  ->  Seq Scan on servers  (cost= 0 . 00 .. 7 . 98  rows= 398  width= 11 ) (actual time= 0 . 019 .. 0 . 929  rows= 398  loops= 1 )
                    ->  Subquery Scan "*SELECT* 2"  (cost= 6739 . 48 .. 6998 . 79  rows= 21  width= 86 ) (actual time= 90 . 596 .. 90 . 683  rows= 2  loops= 1 )
                          ->  Hash Join  (cost= 6739 . 48 .. 6998 . 58  rows= 21  width= 86 ) (actual time= 90 . 588 .. 90 . 667  rows= 2  loops= 1 )
                                Hash Cond: (public.services.server = public.servers.id)
                                ->  Hash Join  (cost= 6726 . 52 .. 6985 . 23  rows= 21  width= 83 ) (actual time= 88 . 623 .. 88 . 665  rows= 2  loops= 1 )
                                      Hash Cond: (public.folders.service = public.services.id)
                                      ->  Nested Loop  (cost= 6712 . 30 .. 6970 . 72  rows= 21  width= 76 ) (actual time= 87 . 004 .. 87 . 037  rows= 2  loops= 1 )
                                            ->  Bitmap Heap Scan on files  (cost= 6712 . 30 .. 6796 . 03  rows= 21  width= 12 ) (actual time= 86 . 963 .. 86 . 967  rows= 2  loops= 1 )
                                                  Recheck Cond: ((to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text)) AND (tth IS NULL) AND (type IS NULL))
                                                  ->  BitmapAnd  (cost= 6712 . 30 .. 6712 . 30  rows= 21  width= 0 ) (actual time= 86 . 950 .. 86 . 950  rows= 0  loops= 1 )
                                                        ->  Bitmap Index Scan on files_name_russian_vector_index  (cost= 0 . 00 .. 157 . 11  rows= 5294  width= 0 ) (actual time= 0 . 096 .. 0 . 096  rows= 48  loops= 1 )
                                                              Index Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                                                        ->  Bitmap Index Scan on files_tth_index  (cost= 0 . 00 .. 6554 . 93  rows= 21235  width= 0 ) (actual time= 86 . 823 .. 86 . 823  rows= 346939  loops= 1 )
                                                              Index Cond: (tth IS NULL)
                                            ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 8 . 31  rows= 1  width= 76 ) (actual time= 0 . 018 .. 0 . 021  rows= 1  loops= 2 )
                                                  Index Cond: (public.folders.id = public.files.path)
                                      ->  Hash  (cost= 9 . 10 .. 9 . 10  rows= 410  width= 15 ) (actual time= 1 . 591 .. 1 . 591  rows= 410  loops= 1 )
                                            ->  Seq Scan on services  (cost= 0 . 00 .. 9 . 10  rows= 410  width= 15 ) (actual time= 0 . 009 .. 0 . 790  rows= 410  loops= 1 )
                                ->  Hash  (cost= 7 . 98 .. 7 . 98  rows= 398  width= 11 ) (actual time= 1 . 855 .. 1 . 855  rows= 398  loops= 1 )
                                      ->  Seq Scan on servers  (cost= 0 . 00 .. 7 . 98  rows= 398  width= 11 ) (actual time= 0 . 011 .. 0 . 754  rows= 398  loops= 1 )
Total runtime:  57302 . 475  ms

Индексы, которые есть в СУБД:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

CREATE INDEX folders_service_index ON folders USING btree (service)
CREATE INDEX tths_count_index ON tths USING btree (count DESC NULLS LAST)
CREATE INDEX files_path_index ON files USING btree (path)
CREATE INDEX services_server_index ON services USING btree (server)
CREATE INDEX services_type_index ON services USING btree (type)
CREATE INDEX folders_rubruc_index ON folders USING btree (rubric)
CREATE INDEX files_type_folder_index ON files USING btree (type) WHERE (type IS NULL)
CREATE INDEX files_name_russian_vector_index ON files USING gin (to_tsvector('russian'::regconfig, (name)::text))
CREATE INDEX files_tth_index ON files USING btree (tth) WHERE (type IS NULL)

Ну что-то ни в какую не хочет оптимизироваться запрос...

...

Рейтинг:

0 / 0

25.07.2008, 16:16

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453077

tadmin

Участник

Сообщения: 1 189

Рейтинг: 0 / 0

- если поиск по %панда% возвращает немного записей, то можно сохранить их во временную таблицу, а уж потом джойнить с остальным хохяйством.
- выделить часть запроса во view, часто это позволяет загнать запрос в более оптимальный план.
Например подзапрос (select distinct on ...)

...

Рейтинг:

0 / 0

25.07.2008, 18:45

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453381

UKY

Гость

tadmin- если поиск по %панда% возвращает немного записей, то можно сохранить их во временную таблицу, а уж потом джойнить с остальным хохяйством.
- выделить часть запроса во view, часто это позволяет загнать запрос в более оптимальный план.
Например подзапрос (select distinct on ...)

Сделал временную таблицу - не помогло. И вид тоже делал - та же ситуация.

P.S.: В основном нагрузка идет на процессор. Жесткий диск не так уж и пыжится.

...

Рейтинг:

0 / 0

26.07.2008, 06:26

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453483

tadmin

Участник

Сообщения: 1 189

Рейтинг: 0 / 0

а если

Код: plaintext

1.
2.
3.
4.

create view files_view as 
select *
from files 
where type is null and tth is null

а потом так:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

select 	servers.ip,
	services.nick,
	ts_headline('russian', folders.path, to_tsquery('панда')),
	NULL,
	files.size,
	NULL,
	NULL,
	NULL AS id,
	NULL AS count
from	files_view 
	join folders on (files_view.path = folders.id)
	join services on (folders.service = services.id)
           join servers on (servers.id = services.server)
where	to_tsvector('russian', files_view.name) @@ to_tsquery('панда')

Кстати, я не понял из схемы данных, нельзя ли кое-где указать inner join ?

...

Рейтинг:

0 / 0

26.07.2008, 13:07

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453498

UKY

Гость

tadminа если

Код: plaintext

1.
2.
3.
4.

create view files_view as 
select *
from files 
where type is null and tth is null

а потом так:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

select 	servers.ip,
	services.nick,
	ts_headline('russian', folders.path, to_tsquery('панда')),
	NULL,
	files.size,
	NULL,
	NULL,
	NULL AS id,
	NULL AS count
from	files_view 
	join folders on (files_view.path = folders.id)
	join services on (folders.service = services.id)
           join servers on (servers.id = services.server)
where	to_tsvector('russian', files_view.name) @@ to_tsquery('панда')

Кстати, я не понял из схемы данных, нельзя ли кое-где указать inner join ?

PostgreSQL
The words INNER and OUTER are optional in all forms. INNER is the default; LEFT, RIGHT, and FULL imply an outer join.

Так что здесь и так используется inner join ;)

Сделал так, как Вы мне посоветовали за исключением того, что убрал "where type is null and tth is null" из вида, т.к. логика страдает:
type - тип файла. Для директории = null
tth - контрольная сумма файла. Для директории = null
А мне надо найти и файлы, и папки.

Запрос

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

select 	servers.ip,
	services.nick,
	ts_headline('russian', folders.path, to_tsquery('панда')),
	NULL,
	files.size,
	NULL,
	NULL,
	NULL AS id,
	NULL AS count
from	servers
	join services	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
where
	files.type is null and
	files.tth is null and
	to_tsvector('russian', files.name) @@ to_tsquery('панда')

работает приемленно быстро.

Главная задача - оптимизация

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

select 	distinct on (count, tths.id, files_view.name)
	servers.ip	AS ip,
	services.nick	AS nick,
	folders.path	AS path,
	ts_headline('russian', files_view.name || '.' || files_view.type, to_tsquery('панда'))
			AS summary,
	files_view.size	AS size,
	tths.added	AS added,
	tths.tth	AS tth,
	tths.id		AS id,
	tths.count	AS count
from	servers
	join services	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files_view	on (files_view.path = folders.id)
	join tths	on (files_view.tth = tths.id)
	join
	(
--explain analyze
	select	distinct on (tths.tth)
		tths.id AS tth_id
	from	files join tths on files.tth = tths.id
	where	files.size >  0  and
		files.type is not null and
		to_tsvector('russian', files.name) @@ to_tsquery('панда')
	) AS inq
	on (tths.id = inq.tth_id)

...

Рейтинг:

0 / 0

26.07.2008, 13:34

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453541

tadmin

Участник

Сообщения: 1 189

Рейтинг: 0 / 0

вот этот

Код: plaintext

  Seq Scan on files  (cost= 0 . 00 .. 260301 . 67  rows= 5293967  width= 286 ) (actual time= 0 . 007 .. 44122 . 366  rows= 5293967  loops= 1 )

ведет себя довольно нагло. Даже интересно.
У меня был похожий случай, победил через view.
У вас большой pg_dump, можете выложить?

...

Рейтинг:

0 / 0

26.07.2008, 15:17

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453703

UKY

Гость

tadminвот этот

Код: plaintext

  Seq Scan on files  (cost= 0 . 00 .. 260301 . 67  rows= 5293967  width= 286 ) (actual time= 0 . 007 .. 44122 . 366  rows= 5293967  loops= 1 )

ведет себя довольно нагло. Даже интересно.
У меня был похожий случай, победил через view.
У вас большой pg_dump, можете выложить?
До меня только сейчас дошло как узнать с какого места в запросе тормоза начинаются %)
pg_dump большеват. да и скорость интернета не очень большая.

сделал set enable_seqscan to off и запрос выполнился за 2,5 секунды:

Код: plaintext

Sort  (cost= 4276639 . 47 .. 4279045 . 56  rows= 962436  width= 408 ) (actual time= 2542 . 133 .. 2542 . 181  rows= 30  loops= 1 )
  Sort Key: "*SELECT* 1".count, "*SELECT* 1".id
  Sort Method:  quicksort  Memory: 33kB
  ->  Append  (cost= 3976023 . 10 .. 4003351 . 98  rows= 962436  width= 408 ) (actual time= 2443 . 632 .. 2541 . 988  rows= 30  loops= 1 )
        ->  Subquery Scan "*SELECT* 1"  (cost= 3976023 . 10 .. 3995271 . 40  rows= 962415  width= 408 ) (actual time= 2443 . 628 .. 2444 . 006  rows= 28  loops= 1 )
              ->  Unique  (cost= 3976023 . 10 .. 3985647 . 25  rows= 962415  width= 408 ) (actual time= 2443 . 622 .. 2443 . 895  rows= 28  loops= 1 )
                    ->  Sort  (cost= 3976023 . 10 .. 3978429 . 14  rows= 962415  width= 408 ) (actual time= 2443 . 617 .. 2443 . 710  rows= 58  loops= 1 )
                          Sort Key: public.tths.count, public.tths.id, public.files.name
                          Sort Method:  quicksort  Memory: 43kB
                          ->  Hash Join  (cost= 85993 . 27 .. 3702741 . 36  rows= 962415  width= 408 ) (actual time= 2312 . 161 .. 2443 . 063  rows= 58  loops= 1 )
                                Hash Cond: (public.folders.service = public.services.id)
                                ->  Hash Join  (cost= 85807 . 13 .. 3677291 . 83  rows= 962415  width= 398 ) (actual time= 2307 . 106 .. 2435 . 514  rows= 58  loops= 1 )
                                      Hash Cond: (public.files.path = public.folders.id)
                                      ->  Nested Loop  (cost= 60363 . 09 .. 3544876 . 51  rows= 962415  width= 334 ) (actual time= 2 . 448 .. 3 . 894  rows= 58  loops= 1 )
                                            ->  Nested Loop  (cost= 60363 . 09 .. 99255 . 48  rows= 4627  width= 56 ) (actual time= 2 . 383 .. 2 . 922  rows= 19  loops= 1 )
                                                  ->  Unique  (cost= 60363 . 09 .. 60386 . 22  rows= 4627  width= 44 ) (actual time= 2 . 365 .. 2 . 568  rows= 19  loops= 1 )
                                                        ->  Sort  (cost= 60363 . 09 .. 60374 . 66  rows= 4627  width= 44 ) (actual time= 2 . 361 .. 2 . 438  rows= 46  loops= 1 )
                                                              Sort Key: public.tths.tth
                                                              Sort Method:  quicksort  Memory: 28kB
                                                              ->  Nested Loop  (cost= 158 . 34 .. 60081 . 40  rows= 4627  width= 44 ) (actual time= 0 . 278 .. 1 . 926  rows= 46  loops= 1 )
                                                                    ->  Bitmap Heap Scan on files  (cost= 158 . 34 .. 18685 . 88  rows= 4940  width= 4 ) (actual time= 0 . 200 .. 0 . 637  rows= 46  loops= 1 )
                                                                          Recheck Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                                                                          Filter: ((type IS NOT NULL) AND (size >  0 ))
                                                                          ->  Bitmap Index Scan on files_name_russian_vector_index  (cost= 0 . 00 .. 157 . 11  rows= 5294  width= 0 ) (actual time= 0 . 161 .. 0 . 161  rows= 48  loops= 1 )
                                                                                Index Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                                                                    ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 37  rows= 1  width= 44 ) (actual time= 0 . 019 .. 0 . 021  rows= 1  loops= 46 )
                                                                          Index Cond: (public.tths.id = public.files.tth)
                                                  ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 38  rows= 1  width= 52 ) (actual time= 0 . 009 .. 0 . 011  rows= 1  loops= 19 )
                                                        Index Cond: (public.tths.id = public.tths.id)
                                            ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 741 . 90  rows= 222  width= 286 ) (actual time= 0 . 025 .. 0 . 036  rows= 3  loops= 19 )
                                                  Index Cond: (public.files.tth = public.tths.id)
                                      ->  Hash  (cost= 16835 . 44 .. 16835 . 44  rows= 341648  width= 76 ) (actual time= 2304 . 389 .. 2304 . 389  rows= 342931  loops= 1 )
                                            ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 16835 . 44  rows= 341648  width= 76 ) (actual time= 0 . 049 .. 1008 . 137  rows= 342931  loops= 1 )
                                ->  Hash  (cost= 181 . 01 .. 181 . 01  rows= 410  width= 18 ) (actual time= 4 . 933 .. 4 . 933  rows= 410  loops= 1 )
                                      ->  Hash Join  (cost= 74 . 66 .. 181 . 01  rows= 410  width= 18 ) (actual time= 1 . 823 .. 4 . 139  rows= 410  loops= 1 )
                                            Hash Cond: (public.servers.id = public.services.server)
                                            ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 99 . 76  rows= 398  width= 11 ) (actual time= 0 . 026 .. 0 . 791  rows= 398  loops= 1 )
                                            ->  Hash  (cost= 69 . 54 .. 69 . 54  rows= 410  width= 15 ) (actual time= 1 . 780 .. 1 . 780  rows= 410  loops= 1 )
                                                  ->  Index Scan using services_pkey on services  (cost= 0 . 00 .. 69 . 54  rows= 410  width= 15 ) (actual time= 0 . 016 .. 0 . 962  rows= 410  loops= 1 )
        ->  Subquery Scan "*SELECT* 2"  (cost= 6352 . 84 .. 8080 . 58  rows= 21  width= 86 ) (actual time= 97 . 706 .. 97 . 881  rows= 2  loops= 1 )
              ->  Nested Loop  (cost= 6352 . 84 .. 8080 . 37  rows= 21  width= 86 ) (actual time= 97 . 698 .. 97 . 866  rows= 2  loops= 1 )
                    ->  Nested Loop  (cost= 6352 . 84 .. 7969 . 07  rows= 21  width= 83 ) (actual time= 97 . 569 .. 97 . 672  rows= 2  loops= 1 )
                          ->  Nested Loop  (cost= 6352 . 84 .. 7832 . 99  rows= 21  width= 76 ) (actual time= 97 . 502 .. 97 . 572  rows= 2  loops= 1 )
                                ->  Bitmap Heap Scan on files  (cost= 6352 . 84 .. 7658 . 31  rows= 21  width= 12 ) (actual time= 97 . 323 .. 97 . 328  rows= 2  loops= 1 )
                                      Recheck Cond: ((to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text)) AND (tth IS NULL))
                                      Filter: (type IS NULL)
                                      ->  BitmapAnd  (cost= 6352 . 84 .. 6352 . 84  rows= 335  width= 0 ) (actual time= 97 . 307 .. 97 . 307  rows= 0  loops= 1 )
                                            ->  Bitmap Index Scan on files_name_russian_vector_index  (cost= 0 . 00 .. 157 . 11  rows= 5294  width= 0 ) (actual time= 0 . 070 .. 0 . 070  rows= 48  loops= 1 )
                                                  Index Cond: (to_tsvector('russian'::regconfig, (name)::text) @@ to_tsquery('панда'::text))
                                            ->  Bitmap Index Scan on files_tth_index  (cost= 0 . 00 .. 6195 . 47  rows= 335285  width= 0 ) (actual time= 97 . 206 .. 97 . 206  rows= 342931  loops= 1 )
                                                  Index Cond: (tth IS NULL)
                                ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 8 . 31  rows= 1  width= 76 ) (actual time= 0 . 105 .. 0 . 107  rows= 1  loops= 2 )
                                      Index Cond: (public.folders.id = public.files.path)
                          ->  Index Scan using services_pkey on services  (cost= 0 . 00 .. 6 . 47  rows= 1  width= 15 ) (actual time= 0 . 037 .. 0 . 039  rows= 1  loops= 2 )
                                Index Cond: (public.services.id = public.folders.service)
                    ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 5 . 28  rows= 1  width= 11 ) (actual time= 0 . 026 .. 0 . 029  rows= 1  loops= 2 )
                          Index Cond: (public.servers.id = public.services.server)
Total runtime:  2543 . 823  ms

Это уже хорошо. Даже очень :)

Но можно ли как-нибудь без принудительной установки параметров оптимизатора этого добиться?

...

Рейтинг:

0 / 0

26.07.2008, 21:32

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453863

tadmin

Участник

Сообщения: 1 189

Рейтинг: 0 / 0

UKY
До меня только сейчас дошло как узнать с какого места в запросе тормоза начинаются %)
pg_dump большеват. да и скорость интернета не очень большая.

Тормоза (по времени) у вас начинались с hash-join's в памяти, но вот вызвала их никчемная туча записей. Это относительно простой случай, потому что в итоге мало записей возвращается, а sec-scan - большой. Могло быть и так, что делается несколько выборок, которые медленно сужают число записей, делают им hash-join, в то время как в самом конце выполняется условие where, которое и так бы все это отрезало. Стоит заставить планировщик применить первым самое жестокое правило, как все начинает летать.

UKY
сделал set enable_seqscan to off и запрос выполнился за 2,5 секунды:
Но можно ли как-нибудь без принудительной установки параметров оптимизатора этого добиться?
При объединении 8-10 таблиц(подзапросов) с кучей условий объединения безумно растет количество возможных планов исполнения. Для 8-10 таблиц оно может измеряться в тысячах. Планировщик видит это и не выбирает аналитически оптимальный, а лишь делает "догадку". (Читать про GQO).

Я замечал (не надо мне верить, это догадки), что при большом числе таблиц даже порядок их перечисления в запросе может радикально повлиять на план исполнения, а небольшое изменение в статистике таблиц может внезапно "опрокинуть" до сих пор работавший план запроса.

В подобной ситуации я добился оптимального плана через комбинацию нескольких view.

Попробуйте разбить ваш запрос на относительно простые части и реализуйте их в виде view так, чтобы финальный запрос можно было построить на их комбинации. Если каждая view будет исполнятся с оптимальным планом, есть шанс на то же самое в целом.

...

Рейтинг:

0 / 0

27.07.2008, 09:52

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453890

MBG

Гость

Попробуйте на набор запросов разбить и отлаживайте их по шагам. Если промежуточный запрос дает мало записей - сохраняйте во временную таблицу, много записей - делайте вид. См.

http://postgrestips.blogspot.com/2007/07/temp.html

...

Рейтинг:

0 / 0

27.07.2008, 11:28

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453900

UKY

Гость

Странно. Сейчас еще раз попробовал сделать при помощи видов, как вы и советовали и все залетало. Вчера не летало.

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.

create or replace temp view find_tths AS
	select	distinct on (tths.tth)
		tths.id AS tth_id
	from	files join tths on files.tth = tths.id
	where	files.size >  0  and
		files.type is not null and
		to_tsvector('russian', files.name) @@ to_tsquery('панда');

explain analyze
select 	distinct on (count, tths.id, files.name)
	servers.ip	AS ip,
	services.nick	AS nick,
	folders.path	AS path,
	ts_headline('russian', files.name || '.' || files.type, to_tsquery('панда'))
			AS summary,
	files.size	AS size,
	tths.added	AS added,
	tths.tth	AS tth,
	tths.id		AS id,
	tths.count	AS count
from	servers
	join services	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
	join tths	on (files.tth = tths.id)
	join
	(
--explain analyze
		select * from find_tths
	) AS inq
	on (tths.id = inq.tth_id)
UNION ALL
--explain analyze
select 	servers.ip,
	services.nick,
	ts_headline('russian', folders.path, to_tsquery('панда')),
	NULL,
	files.size,
	NULL,
	NULL,
	NULL AS id,
	NULL AS count
from	servers
	join services	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
where
	files.type is null and
	files.tth is null and
	to_tsvector('russian', files.name) @@ to_tsquery('панда')
order 	by count desc NULLS LAST, id;

Только вот одна маленькая проблема: в вИды нельзя передавать параметры. А мне надо как-то передать строку "панда". Как лечить?

...

Рейтинг:

0 / 0

27.07.2008, 11:43

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453926

MBG

Гость

Можно сделать функцию, которая принимает параметры и создает нужные таблицы и виды (временные или постоянные). Если вы смотрели указанную ссылку, там так и делается.

...

Рейтинг:

0 / 0

27.07.2008, 12:23

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453976

UKY

Гость

Почему-то не получется так сделать:

Код: plaintext

create or replace function find_all(pattern varchar) returns record as
$$
DECLARE
	rec record;
begin
	create or replace temp view find_tths AS
	select	distinct on (tths.tth)
		tths.id AS tth_id
	from	files join tths on files.tth = tths.id
	where	files.size >  0  and
		files.type is not null and
		to_tsvector('russian', files.name) @@ to_tsquery(pattern);

	FOR rec IN
	select 	distinct on (count, tths.id, files.name)
		servers.ip	AS ip,
		services.nick	AS nick,
		folders.path	AS path,
		ts_headline('russian', files.name || '.' || files.type, to_tsquery(pattern))
				AS summary,
		files.size	AS size,
		tths.added	AS added,
		tths.tth	AS tth,
		tths.id		AS id,
		tths.count	AS count
	from	servers
		join services	on (servers.id = services.server)
		join folders	on (folders.service = services.id)
		join files	on (files.path = folders.id)
		join tths	on (files.tth = tths.id)
		join
		(
	--explain analyze
			select * from find_tths
		) AS inq
		on (tths.id = inq.tth_id)
	UNION ALL
	--explain analyze
	select 	servers.ip,
		services.nick,
		ts_headline('russian', folders.path, to_tsquery(pattern)),
		NULL,
		files.size,
		NULL,
		NULL,
		NULL AS id,
		NULL AS count
	from	servers
		join services	on (servers.id = services.server)
		join folders	on (folders.service = services.id)
		join files	on (files.path = folders.id)
	where
		files.type is null and
		files.tth is null and
		to_tsvector('russian', files.name) @@ to_tsquery(pattern)
	order 	by count desc NULLS LAST, id
	LOOP
		return rec;
	END LOOP;
end;
$$
language plpgsql;

Когда вызываю

Код: plaintext

select * from find_all('панда') AS (ip inet, nick varchar, path varchar, summary varchar, size bigint, added date, tth varchar, id integer, count integer)

Пишет вот такую ошибку:
PL/pgSQL function "find_all" line 4 at SQL statement
ERROR: there is no parameter $1
CONTEXT: SQL statement "create or replace temp view find_tths AS select distinct on (tths.tth) tths.id AS tth_id from files join tths on files.tth = tths.id where files.size > 0 and files.type is not null and to_tsvector('russian', files.name) @@ to_tsquery( $1 )"

Странно это все как-то...

...

Рейтинг:

0 / 0

27.07.2008, 13:12

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453991

MBG

Гость

Используйте EXECUTE. Сейчас вы пытаетесь выполнить свой запрос как уже готовый, а на самом деле вам его сначала надо подговить, обеспечив подстановку переменных.

...

Рейтинг:

0 / 0

27.07.2008, 13:25

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35453998

UKY

Гость

Сейчас попробовал сделать в функции без параметров и опять начало тормозить.
Не одно так другое тормозит :)

...

Рейтинг:

0 / 0

27.07.2008, 13:29

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454002

UKY

Гость

MBGИспользуйте EXECUTE. Сейчас вы пытаетесь выполнить свой запрос как уже готовый, а на самом деле вам его сначала надо подговить, обеспечив подстановку переменных.
Ой, точно! Что-то я уже тупить начинаю %) Видимо, нужно развеяться :)

Но из функции выборка делается дольше. Это факт. Видимо, из-за того, что она использует курсор в цикле.

...

Рейтинг:

0 / 0

27.07.2008, 13:37

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454009

MBG

Гость

А зачем вам там курсор? В функции надо создать таблицу, а потом ее прочитать после выхода из функции. Не надо пытаться вернуть таблицу из функции, в постгресе такая операция весьма медленная, не путайте с ораклом.

...

Рейтинг:

0 / 0

27.07.2008, 13:41

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454016

UKY

Гость

Нет, к сожалению, я ошибся. Все намного хуже.
Сейчас сделал так, как было раньше без функции. Опять тормозит. Так что функция здесь не при чем.

Посмотрел план: опять производится seqscan по files. План поменялся.

...

Рейтинг:

0 / 0

27.07.2008, 13:46

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454019

MBG

Гость

Обычное дело. Это, собственно, одна из причин, по которой я сам отказался от постгреса. Оптимизировать запрос можно, но изменится количество записей в одной из больших таблиц на порядок и снова несколько дней проведете в переборе вариантов объединений таблиц и создании временных таблиц/видов. А вообще ваш запрос довольно простой, до 10 таблиц оптимизировать можно. Попробуйте переписать запрос, имхо он написан не оптимально.

...

Рейтинг:

0 / 0

27.07.2008, 13:57

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454029

MBG

Гость

Вы, случаем, не на рабочей системе тестируете? Планировщик постгреса так устроен, что подстраивается во время работы, то есть если у вас на системе выполнено много одинаковых запросов план будет отличаться от плана первого выполнения запроса.

...

Рейтинг:

0 / 0

27.07.2008, 14:14

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454055

UKY

Гость

Нет. Я работаю на статичной базе. Тестирую, экспериментирую, пробую :)

...

Рейтинг:

0 / 0

27.07.2008, 15:34

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454149

KRED

Участник

Откуда: München/Augsburg (Germany)

Сообщения: 611

Рейтинг: 0 / 0

Может стоить попробовать условие "text like '%панда%'" разбить на два "text like 'панда%' OR text like '%панда'" ну и по полю построить два индекса - один обычный , а второй со спецальными опциями (поищи на форуме) для "обратного %" "%панда" ?

...

Рейтинг:

0 / 0

27.07.2008, 19:13

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454250

tadmin

Участник

Сообщения: 1 189

Рейтинг: 0 / 0

Забыл.

Код: plaintext

effective_cache_size = 1G #

This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used.

Т.е. если ваши индексы не лезут в предполагаемый размер дискового кеша, то запрос сорвется в seq_scan

И еще попробуйте:

Код: plaintext

geqo_threshold =  32  # а может и больше..
from_collapse_limit = 16

Это заставит планировщик даже для сложных запросов применять аналитический метод. Т.е. планировщик будет искать действительно оптимальный план детерминистским методом, а не переключаться на эвристику.

...

Рейтинг:

0 / 0

27.07.2008, 22:47

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454349

UKY

Гость

Неа. Не помогает. Пока что рулит только set enable_seqscan to on :)

...

Рейтинг:

0 / 0

28.07.2008, 05:49

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454350

UKY

Гость

UKYНеа. Не помогает. Пока что рулит только set enable_seqscan to on :)
Точнее off ;)

...

Рейтинг:

0 / 0

28.07.2008, 05:49

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35454564

tadmin

Участник

Сообщения: 1 189

Рейтинг: 0 / 0

Если и по колесу стучали и зажигание проверяли (перезапускали после каждого изменения конфига и делали vacuum analyze), тогда надо играть с комбинацией из нескольких view.

Далее советы очень трудно давать, не видя базы. Чужие explain analyze не так наглядны, заочно только великие адепты могут помочь.

...

Рейтинг:

0 / 0

28.07.2008, 10:10

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35455058

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKY

Код: plaintext

1.
2.
3.
4.
5.

->  Hash Join  (cost= 90 . 54 .. 280244 . 78  rows= 2080  width= 334 ) (actual time= 644 . 000 .. 57193 . 141  rows= 130  loops= 1 )
    Hash Cond: (public.files.tth = tths.id)
    ->  Seq Scan on files  (cost= 0 . 00 .. 260301 . 67  rows= 5293967  width= 286 ) (actual time= 0 . 007 .. 44122 . 366  rows= 5293967  loops= 1 )
    ->  Hash  (cost= 90 . 41 .. 90 . 41  rows= 10  width= 56 ) (actual time= 3 . 629 .. 3 . 629  rows= 24  loops= 1 )

CREATE INDEX files_tth_index ON files USING btree (tth) WHERE (type IS NULL)

в первом подзапросе (от SELECT до UNION) нет условия WHERE files.type IS NULL. поэтому уберите это условие из индекса по files(tth). покажите получившийся EXPLAIN ANALYZE.

...

Рейтинг:

0 / 0

28.07.2008, 13:00

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35455125

Author the new one

Участник

Сообщения: 300

Рейтинг: 0 / 0

UKYДоброго времени суток, многоуважаемые :)

Помогите, пожалуйста, оптимизировать запрос:

Я бы начал с того, что убедился, что существуют индексы:

Код: plaintext

1.
2.
3.

tths(id)
files(tth,path)
folders(id, sevice)
services(id)

Кроме того, в позапросе во from (который select distinct on (tths.tth)...) от греха подальше поставил бы limit 10, скажем.

...

Рейтинг:

0 / 0

28.07.2008, 13:16

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457033

UKY

Гость

LeXa NalBat UKY

Код: plaintext

CREATE INDEX files_tth_index ON files USING btree (tth) WHERE (type IS NULL)

в первом подзапросе (от SELECT до UNION) нет условия WHERE files.type IS NULL. поэтому уберите это условие из индекса по files(tth). покажите получившийся EXPLAIN ANALYZE.
Убрал условие.

Сделал индекс

Код: plaintext

CREATE INDEX files_name_tsvector_index	ON files USING gin(to_tsvector('russian', name || coalesce('.' || type, '')))
									TABLESPACE indexspace;

Сделал VACUUM FULL ANALYZE.

Немного переделал запрос. Получился вот такой:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

select 	distinct on (count, tth_id, name)
	service.prefix	AS prefix,
	services.hub	AS hub,
	servers.ip	AS ip,
	services.nick	AS nick,
	folders.path	AS path,
	files.name	AS name,
	files.type	AS type,
	files.size	AS size,
	tths.count	AS count,
	tths.id		AS tth_id
	--ts_headline('russian', files.name || '.' || files.type, to_tsquery(pattern))
from	service
	join services	on (service.id = services.type)
	join servers	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
	join tths	on (files.tth = tths.id)
	join
	(
--explain analyze
		select	distinct on (tths.id)
			tths.id AS tth_id
		from 	files join tths on (files.tth = tths.id)
		where	to_tsvector('russian', files.name || coalesce('.' || files.type, '')) @@ to_tsquery('панда')
	) AS inq
	on (tths.id = inq.tth_id)
order 	by count desc, tth_id, name

Но один фиг использется последовательный перебор по таблице файлов:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.

Unique  (cost= 723161 . 22 .. 733706 . 45  rows= 1054523  width= 378 ) (actual time= 67955 . 121 .. 67955 . 390  rows= 29  loops= 1 )
  ->  Sort  (cost= 723161 . 22 .. 725797 . 52  rows= 1054523  width= 378 ) (actual time= 67955 . 115 .. 67955 . 214  rows= 59  loops= 1 )
        Sort Key: public.tths.count, public.tths.id, public.files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Hash Join  (cost= 109233 . 81 .. 433842 . 40  rows= 1054523  width= 378 ) (actual time= 8333 . 997 .. 67954 . 163  rows= 59  loops= 1 )
              Hash Cond: (public.files.path = folders.id)
              ->  Hash Join  (cost= 92216 . 48 .. 397052 . 76  rows= 1054523  width= 291 ) (actual time= 4264 . 654 .. 63884 . 350  rows= 59  loops= 1 )
                    Hash Cond: (public.files.tth = public.tths.id)
                    ->  Seq Scan on files  (cost= 0 . 00 .. 258449 . 20  rows= 5290120  width= 287 ) (actual time= 0 . 046 .. 50781 . 539  rows= 5302841  loops= 1 )
                    ->  Hash  (cost= 92154 . 48 .. 92154 . 48  rows= 4960  width= 12 ) (actual time= 2 . 047 .. 2 . 047  rows= 19  loops= 1 )
                          ->  Nested Loop  (cost= 56516 . 24 .. 92154 . 48  rows= 4960  width= 12 ) (actual time= 1 . 429 .. 2 . 001  rows= 19  loops= 1 )
                                ->  Unique  (cost= 56516 . 24 .. 56541 . 04  rows= 4960  width= 4 ) (actual time= 1 . 412 .. 1 . 605  rows= 19  loops= 1 )
                                      ->  Sort  (cost= 56516 . 24 .. 56528 . 64  rows= 4960  width= 4 ) (actual time= 1 . 407 .. 1 . 486  rows= 47  loops= 1 )
                                            Sort Key: public.tths.id
                                            Sort Method:  quicksort  Memory: 27kB
                                            ->  Nested Loop  (cost= 178 . 60 .. 56211 . 79  rows= 4960  width= 4 ) (actual time= 0 . 152 .. 1 . 283  rows= 47  loops= 1 )
                                                  ->  Bitmap Heap Scan on files  (cost= 178 . 60 .. 18705 . 13  rows= 5290  width= 4 ) (actual time= 0 . 120 .. 0 . 243  rows= 49  loops= 1 )
                                                        Recheck Cond: (to_tsvector('russian'::regconfig, ((name)::text || COALESCE(('.'::text || (type)::text), ''::text))) @@ to_tsquery('панда'::text))
                                                        ->  Bitmap Index Scan on files_name_tsvector_index  (cost= 0 . 00 .. 177 . 28  rows= 5290  width= 0 ) (actual time= 0 . 107 .. 0 . 107  rows= 49  loops= 1 )
                                                              Index Cond: (to_tsvector('russian'::regconfig, ((name)::text || COALESCE(('.'::text || (type)::text), ''::text))) @@ to_tsquery('панда'::text))
                                                  ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 7 . 08  rows= 1  width= 4 ) (actual time= 0 . 012 .. 0 . 014  rows= 1  loops= 49 )
                                                        Index Cond: (public.tths.id = public.files.tth)
                                ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 7 . 16  rows= 1  width= 8 ) (actual time= 0 . 007 .. 0 . 009  rows= 1  loops= 19 )
                                      Index Cond: (public.tths.id = public.tths.id)
              ->  Hash  (cost= 12730 . 06 .. 12730 . 06  rows= 342982  width= 99 ) (actual time= 4069 . 138 .. 4069 . 138  rows= 343308  loops= 1 )
                    ->  Hash Join  (cost= 39 . 24 .. 12730 . 06  rows= 342982  width= 99 ) (actual time= 8 . 425 .. 2901 . 313  rows= 343308  loops= 1 )
                          Hash Cond: (folders.service = services.id)
                          ->  Seq Scan on folders  (cost= 0 . 00 .. 7974 . 82  rows= 342982  width= 76 ) (actual time= 0 . 020 .. 875 . 335  rows= 343308  loops= 1 )
                          ->  Hash  (cost= 34 . 06 .. 34 . 06  rows= 414  width= 31 ) (actual time= 8 . 382 .. 8 . 382  rows= 414  loops= 1 )
                                ->  Hash Join  (cost= 13 . 04 .. 34 . 06  rows= 414  width= 31 ) (actual time= 1 . 997 .. 7 . 182  rows= 414  loops= 1 )
                                      Hash Cond: (services.server = servers.id)
                                      ->  Nested Loop  (cost= 0 . 00 .. 15 . 33  rows= 414  width= 28 ) (actual time= 0 . 032 .. 3 . 038  rows= 414  loops= 1 )
                                            Join Filter: (service.id = services.type)
                                            ->  Seq Scan on service  (cost= 0 . 00 .. 1 . 01  rows= 1  width= 13 ) (actual time= 0 . 005 .. 0 . 007  rows= 1  loops= 1 )
                                            ->  Seq Scan on services  (cost= 0 . 00 .. 9 . 14  rows= 414  width= 23 ) (actual time= 0 . 012 .. 0 . 864  rows= 414  loops= 1 )
                                      ->  Hash  (cost= 8 . 02 .. 8 . 02  rows= 402  width= 11 ) (actual time= 1 . 946 .. 1 . 946  rows= 402  loops= 1 )
                                            ->  Seq Scan on servers  (cost= 0 . 00 .. 8 . 02  rows= 402  width= 11 ) (actual time= 0 . 013 .. 0 . 922  rows= 402  loops= 1 )
Total runtime:  67955 . 752  ms

Делал вид по

Код: plaintext

1.
2.
3.
4.

select	distinct on (tths.id)
	tths.id AS tth_id
from 	files join tths on (files.tth = tths.id)
where	to_tsvector('russian', files.name || coalesce('.' || files.type, '')) @@ to_tsquery('панда')

Тоже самое.

И возник еще один вопрос:
Почему не используется индекс

Код: plaintext

CREATE INDEX tths_count_index		ON tths(count DESC NULLS LAST)	TABLESPACE indexspace;

для сортировки результатов выдачи? (даже если пишу order by count desc nulls last)

...

Рейтинг:

0 / 0

29.07.2008, 10:25

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457204

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKY LeXa NalBatв первом подзапросе (от SELECT до UNION) нет условия WHERE files.type IS NULL. поэтому уберите это условие из индекса по files(tth). покажите получившийся EXPLAIN ANALYZE.Убрал условие.

Немного переделал запрос.не так. пробуйте запрос через функцию tth_search при наличии индекса без дополнительных условий по files(tth)

UKYИ возник еще один вопрос:
Почему не используется индекс tths(count DESC NULLS LAST) для сортировки результатов выдачи? (даже если пишу order by count desc nulls last)наверное потому, что индекс только по одному полю count, а order by по трем полям count desc, tth_id, name. но это не важно, потому что 59 строк сортируются быстро.

...

Рейтинг:

0 / 0

29.07.2008, 11:17

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457409

UKY

Гость

LeXa NalBat
не так. пробуйте запрос через функцию tth_search при наличии индекса без дополнительных условий по files(tth)

Да! Получилось! :)

Вот окончательный результат:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

create or replace function tth_search(pattern text) returns setof integer as
$$
BEGIN
	return query
	select	distinct on (tths.id)
		tths.id
	from 	files join tths on (files.tth = tths.id)
	where	to_tsvector('russian', files.name || coalesce('.' || files.type, '')) @@ to_tsquery(pattern);
END;
$$
language plpgsql rows  20 ;

explain analyze
select 	distinct on (count, tth_id, name)
	service.prefix	AS prefix,
	services.hub	AS hub,
	servers.ip	AS ip,
	services.nick	AS nick,
	folders.path	AS path,
	files.name	AS name,
	files.type	AS type,
	files.size	AS size,
	tths.count	AS count,
	tths.id		AS tth_id
	--ts_headline('russian', files.name || '.' || files.type, to_tsquery(pattern))
from	service
	join services	on (service.id = services.type)
	join servers	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
	join tths	on (files.tth = tths.id)
	join
	(
--explain analyze
		select tth_search from tth_search('панда')
	) AS inq
	on (tths.id = inq.tth_search)
order 	by count desc, tth_id, name

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

Unique  (cost= 28453 . 56 .. 28494 . 00  rows= 4044  width= 376 ) (actual time= 2100 . 627 .. 2100 . 892  rows= 29  loops= 1 )
  ->  Sort  (cost= 28453 . 56 .. 28463 . 67  rows= 4044  width= 376 ) (actual time= 2100 . 622 .. 2100 . 721  rows= 59  loops= 1 )
        Sort Key: tths.count, tths.id, files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Hash Join  (cost= 12318 . 18 .. 28211 . 29  rows= 4044  width= 376 ) (actual time= 2097 . 461 .. 2099 . 437  rows= 59  loops= 1 )
              Hash Cond: (folders.service = services.id)
              ->  Hash Join  (cost= 12278 . 95 .. 28116 . 45  rows= 4044  width= 353 ) (actual time= 2089 . 254 .. 2090 . 773  rows= 59  loops= 1 )
                    Hash Cond: (files.path = folders.id)
                    ->  Nested Loop  (cost= 0 . 00 .. 15761 . 68  rows= 4044  width= 290 ) (actual time= 0 . 970 .. 2 . 083  rows= 59  loops= 1 )
                          ->  Nested Loop  (cost= 0 . 00 .. 176 . 28  rows= 20  width= 12 ) (actual time= 0 . 947 .. 1 . 324  rows= 19  loops= 1 )
                                ->  Function Scan on tth_search  (cost= 0 . 00 .. 5 . 20  rows= 20  width= 4 ) (actual time= 0 . 925 .. 0 . 959  rows= 19  loops= 1 )
                                ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 54  rows= 1  width= 8 ) (actual time= 0 . 010 .. 0 . 012  rows= 1  loops= 19 )
                                      Index Cond: (tths.id = tth_search.tth_search)
                          ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 776 . 57  rows= 216  width= 286 ) (actual time= 0 . 008 .. 0 . 017  rows= 3  loops= 19 )
                                Index Cond: (files.tth = tths.id)
                    ->  Hash  (cost= 7982 . 31 .. 7982 . 31  rows= 343731  width= 75 ) (actual time= 2088 . 048 .. 2088 . 048  rows= 343308  loops= 1 )
                          ->  Seq Scan on folders  (cost= 0 . 00 .. 7982 . 31  rows= 343731  width= 75 ) (actual time= 0 . 085 .. 1098 . 678  rows= 343308  loops= 1 )
              ->  Hash  (cost= 34 . 06 .. 34 . 06  rows= 414  width= 31 ) (actual time= 8 . 183 .. 8 . 183  rows= 414  loops= 1 )
                    ->  Hash Join  (cost= 13 . 04 .. 34 . 06  rows= 414  width= 31 ) (actual time= 2 . 201 .. 7 . 043  rows= 414  loops= 1 )
                          Hash Cond: (services.server = servers.id)
                          ->  Nested Loop  (cost= 0 . 00 .. 15 . 33  rows= 414  width= 28 ) (actual time= 0 . 038 .. 2 . 909  rows= 414  loops= 1 )
                                Join Filter: (service.id = services.type)
                                ->  Seq Scan on service  (cost= 0 . 00 .. 1 . 01  rows= 1  width= 13 ) (actual time= 0 . 011 .. 0 . 013  rows= 1  loops= 1 )
                                ->  Seq Scan on services  (cost= 0 . 00 .. 9 . 14  rows= 414  width= 23 ) (actual time= 0 . 010 .. 0 . 885  rows= 414  loops= 1 )
                          ->  Hash  (cost= 8 . 02 .. 8 . 02  rows= 402  width= 11 ) (actual time= 2 . 142 .. 2 . 142  rows= 402  loops= 1 )
                                ->  Seq Scan on servers  (cost= 0 . 00 .. 8 . 02  rows= 402  width= 11 ) (actual time= 0 . 013 .. 1 . 132  rows= 402  loops= 1 )
Total runtime:  2101 . 217  ms

Спасибо всем огромное! Вы мне очень сильно помогли! Столько идей, сколько знаний от вас получил, что аж не перечесть :)

Спасибо еще раз :)

...

Рейтинг:

0 / 0

29.07.2008, 12:08

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457443

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKYДа! Получилось! :)ура!

UKY

Код: plaintext

1.
2.
3.
4.

              ->  Hash Join  (cost= 12278 . 95 .. 28116 . 45  rows= 4044  width= 353 ) (actual time= 2089 . 254 .. 2090 . 773  rows= 59  loops= 1 )
                    Hash Cond: (files.path = folders.id)
                    ->  Nested Loop  (cost= 0 . 00 .. 15761 . 68  rows= 4044  width= 290 ) (actual time= 0 . 970 .. 2 . 083  rows= 59  loops= 1 )
                    ->  Hash  (cost= 7982 . 31 .. 7982 . 31  rows= 343731  width= 75 ) (actual time= 2088 . 048 .. 2088 . 048  rows= 343308  loops= 1 )
                          ->  Seq Scan on folders  (cost= 0 . 00 .. 7982 . 31  rows= 343731  width= 75 ) (actual time= 0 . 085 .. 1098 . 678  rows= 343308  loops= 1 )

сделайте индекс по folders(id), должно заметно ускориться, покажите explain analyze.

...

Рейтинг:

0 / 0

29.07.2008, 12:18

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457507

UKY

Гость

Да, я сам заметил, что все еще последовательный перебор используется. И не только по таблице папок...

Вот какие индексы есть в системе:

Код: plaintext

select indexdef from pg_indexes where indexname not like 'pg_%'

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

CREATE UNIQUE INDEX servers_pkey ON servers USING btree (id)
CREATE UNIQUE INDEX service_pkey ON service USING btree (id)
CREATE INDEX services_type_index ON services USING btree (type)
CREATE INDEX services_server_index ON services USING btree (server)
CREATE UNIQUE INDEX services_pkey ON services USING btree (id)
CREATE INDEX folders_rubruc_index ON folders USING btree (rubric)
CREATE INDEX folders_service_index ON folders USING btree (service)
CREATE UNIQUE INDEX folders_pkey ON folders USING btree (id)
CREATE UNIQUE INDEX unique_tths_tth_size ON tths USING btree (tth, size)
CREATE INDEX tths_count_index ON tths USING btree (count DESC NULLS LAST)
CREATE UNIQUE INDEX tths_tth_key ON tths USING btree (tth)
CREATE UNIQUE INDEX tths_pkey ON tths USING btree (id)
CREATE INDEX files_type_index ON files USING btree (type)
CREATE INDEX files_tth_index ON files USING btree (tth)
CREATE INDEX files_name_tsvector_index ON files USING gin (to_tsvector('russian'::regconfig, ((name)::text || COALESCE(('.'::text || (type)::text), ''::text))))
CREATE INDEX files_path_index ON files USING btree (path)
CREATE UNIQUE INDEX files_pkey ON files USING btree (id)
CREATE UNIQUE INDEX streets_pkey ON streets USING btree (id)
CREATE UNIQUE INDEX houses_pkey ON houses USING btree (id)
CREATE UNIQUE INDEX networks_pkey ON networks USING btree (id)

Так что они и здесь почему-то не используются... Это же лечится? 0:)
P.S.:
Если сделать set enable_seqscan to off то запрос выполняется за 179 ms О_о
Я таких скоростей никогда не видел :) Даже подумал нафиг новый сервер покупать и этот неплохо справляется... 0:)

...

Рейтинг:

0 / 0

29.07.2008, 12:34

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457584

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKYДа, я сам заметил, что все еще последовательный перебор используется. И не только по таблице папок...

Вот какие индексы есть в системе:
...
CREATE UNIQUE INDEX folders_pkey ON folders USING btree (id)
...

Так что они и здесь почему-то не используются... Это же лечится? 0:)

Если сделать set enable_seqscan to off то запрос выполняется за 179 ms О_ода, нужный индекс есть. может получиться запинать до десятков миллисекунд. покажите пожалуйста explain analyze с set enable_seqscan to off, который 179 ms.

Код: plaintext

->  Index Scan using files_tth_index on files (... rows= 216  ...) (actual ... rows= 3  ...)
    Index Cond: (files.tth = tths.id)

здесь постгрес сильно ошибается в оценке кол-ва строк, удовлетворяющих условию files.tth=$1. и наверное вследствии этого выбирает неоптимальный план.

попробуйте выполнить запрос после сбора самой подробной статистики по этой колонке:
ALTER TABLE files ALTER COLUMN tths SET STATISTICS 1000;
ANALYZE files ( tths );
EXPLAIN ANALYZE SELECT ...

...

Рейтинг:

0 / 0

29.07.2008, 12:55

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457628

UKY

Гость

На кэшированных данных еще быстрее (с set enable_seqscan to off):

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

Unique  (cost= 32206 . 46 .. 32246 . 90  rows= 4044  width= 376 ) (actual time= 11 . 473 .. 11 . 828  rows= 29  loops= 1 )
  ->  Sort  (cost= 32206 . 46 .. 32216 . 57  rows= 4044  width= 376 ) (actual time= 11 . 468 .. 11 . 604  rows= 59  loops= 1 )
        Sort Key: tths.count, tths.id, files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Hash Join  (cost= 99 . 79 .. 31964 . 20  rows= 4044  width= 376 ) (actual time= 6 . 355 .. 10 . 481  rows= 59  loops= 1 )
              Hash Cond: (services.server = servers.id)
              ->  Nested Loop  (cost= 35 . 64 .. 31844 . 44  rows= 4044  width= 373 ) (actual time= 4 . 291 .. 7 . 338  rows= 59  loops= 1 )
                    Join Filter: (services.type = service.id)
                    ->  Index Scan using service_pkey on service  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 13 ) (actual time= 0 . 020 .. 0 . 023  rows= 1  loops= 1 )
                    ->  Hash Join  (cost= 35 . 64 .. 31785 . 62  rows= 4044  width= 368 ) (actual time= 4 . 259 .. 7 . 052  rows= 59  loops= 1 )
                          Hash Cond: (folders.service = services.id)
                          ->  Nested Loop  (cost= 0 . 00 .. 31694 . 38  rows= 4044  width= 353 ) (actual time= 1 . 733 .. 4 . 214  rows= 59  loops= 1 )
                                ->  Nested Loop  (cost= 0 . 00 .. 15761 . 68  rows= 4044  width= 290 ) (actual time= 1 . 704 .. 2 . 894  rows= 59  loops= 1 )
                                      ->  Nested Loop  (cost= 0 . 00 .. 176 . 28  rows= 20  width= 12 ) (actual time= 1 . 682 .. 2 . 153  rows= 19  loops= 1 )
                                            ->  Function Scan on tth_search  (cost= 0 . 00 .. 5 . 20  rows= 20  width= 4 ) (actual time= 1 . 645 .. 1 . 686  rows= 20  loops= 1 )
                                            ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 54  rows= 1  width= 8 ) (actual time= 0 . 012 .. 0 . 015  rows= 1  loops= 20 )
                                                  Index Cond: (tths.id = tth_search.tth_search)
                                      ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 776 . 57  rows= 216  width= 286 ) (actual time= 0 . 012 .. 0 . 021  rows= 3  loops= 19 )
                                            Index Cond: (files.tth = tths.id)
                                ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 3 . 93  rows= 1  width= 75 ) (actual time= 0 . 012 .. 0 . 014  rows= 1  loops= 59 )
                                      Index Cond: (folders.id = files.path)
                          ->  Hash  (cost= 30 . 46 .. 30 . 46  rows= 414  width= 23 ) (actual time= 2 . 495 .. 2 . 495  rows= 414  loops= 1 )
                                ->  Index Scan using services_type_index on services  (cost= 0 . 00 .. 30 . 46  rows= 414  width= 23 ) (actual time= 0 . 025 .. 1 . 250  rows= 414  loops= 1 )
              ->  Hash  (cost= 59 . 13 .. 59 . 13  rows= 402  width= 11 ) (actual time= 2 . 039 .. 2 . 039  rows= 402  loops= 1 )
                    ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 59 . 13  rows= 402  width= 11 ) (actual time= 0 . 026 .. 1 . 043  rows= 402  loops= 1 )
Total runtime:  12 . 142  ms

Сделал статистику побольше, как Вы и советовали. Теперь план практически такой же, как и с set enable_seqscan to off:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.

Unique  (cost= 1212 . 20 .. 1213 . 37  rows= 117  width= 376 ) (actual time= 9 . 313 .. 9 . 673  rows= 29  loops= 1 )
  ->  Sort  (cost= 1212 . 20 .. 1212 . 49  rows= 117  width= 376 ) (actual time= 9 . 307 .. 9 . 420  rows= 59  loops= 1 )
        Sort Key: tths.count, tths.id, files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Hash Join  (cost= 27 . 36 .. 1208 . 18  rows= 117  width= 376 ) (actual time= 4 . 695 .. 8 . 365  rows= 59  loops= 1 )
              Hash Cond: (services.server = servers.id)
              ->  Nested Loop  (cost= 14 . 32 .. 1193 . 53  rows= 117  width= 373 ) (actual time= 2 . 795 .. 6 . 169  rows= 59  loops= 1 )
                    Join Filter: (services.type = service.id)
                    ->  Seq Scan on service  (cost= 0 . 00 .. 1 . 01  rows= 1  width= 13 ) (actual time= 0 . 004 .. 0 . 008  rows= 1  loops= 1 )
                    ->  Hash Join  (cost= 14 . 32 .. 1191 . 05  rows= 117  width= 368 ) (actual time= 2 . 781 .. 5 . 904  rows= 59  loops= 1 )
                          Hash Cond: (folders.service = services.id)
                          ->  Nested Loop  (cost= 0 . 00 .. 1175 . 13  rows= 117  width= 353 ) (actual time= 0 . 521 .. 3 . 322  rows= 59  loops= 1 )
                                ->  Nested Loop  (cost= 0 . 00 .. 714 . 17  rows= 117  width= 290 ) (actual time= 0 . 496 .. 1 . 852  rows= 59  loops= 1 )
                                      ->  Nested Loop  (cost= 0 . 00 .. 176 . 28  rows= 20  width= 12 ) (actual time= 0 . 474 .. 0 . 929  rows= 19  loops= 1 )
                                            ->  Function Scan on tth_search  (cost= 0 . 00 .. 5 . 20  rows= 20  width= 4 ) (actual time= 0 . 440 .. 0 . 483  rows= 20  loops= 1 )
                                            ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 54  rows= 1  width= 8 ) (actual time= 0 . 012 .. 0 . 014  rows= 1  loops= 20 )
                                                  Index Cond: (tths.id = tth_search.tth_search)
                                      ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 26 . 82  rows= 6  width= 286 ) (actual time= 0 . 011 .. 0 . 031  rows= 3  loops= 19 )
                                            Index Cond: (files.tth = tths.id)
                                ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 3 . 93  rows= 1  width= 75 ) (actual time= 0 . 013 .. 0 . 016  rows= 1  loops= 59 )
                                      Index Cond: (folders.id = files.path)
                          ->  Hash  (cost= 9 . 14 .. 9 . 14  rows= 414  width= 23 ) (actual time= 2 . 238 .. 2 . 238  rows= 414  loops= 1 )
                                ->  Seq Scan on services  (cost= 0 . 00 .. 9 . 14  rows= 414  width= 23 ) (actual time= 0 . 013 .. 1 . 184  rows= 414  loops= 1 )
              ->  Hash  (cost= 8 . 02 .. 8 . 02  rows= 402  width= 11 ) (actual time= 1 . 877 .. 1 . 877  rows= 402  loops= 1 )
                    ->  Seq Scan on servers  (cost= 0 . 00 .. 8 . 02  rows= 402  width= 11 ) (actual time= 0 . 022 .. 0 . 926  rows= 402  loops= 1 )
Total runtime:  9 . 985  ms

Сижу теперь и офигеваю: с 70 секунд ускорить выборку до 10 мс... Офигеть.

...

Рейтинг:

0 / 0

29.07.2008, 13:07

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457746

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKYНа кэшированных данных еще быстрее (с set enable_seqscan to off):

Код: plaintext

1.
2.
3.
4.

->  Hash  (cost= 30 . 46 .. 30 . 46  rows= 414  width= 23 ) (actual time= 2 . 495 .. 2 . 495  rows= 414  loops= 1 )
    ->  Index Scan using services_type_index on services  (cost= 0 . 00 .. 30 . 46  rows= 414  width= 23 ) (actual time= 0 . 025 .. 1 . 250  rows= 414  loops= 1 )

->  Hash  (cost= 59 . 13 .. 59 . 13  rows= 402  width= 11 ) (actual time= 2 . 039 .. 2 . 039  rows= 402  loops= 1 )
    ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 59 . 13  rows= 402  width= 11 )

здесь неоптимально.

попробуйте после выполнения двух команд:
set enable_seqscan to off;
set enable_hashjoin to off;

...

Рейтинг:

0 / 0

29.07.2008, 13:34

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457836

UKY

Гость

После долгих перезапусков выборки удалось получить вот такую вот картину (:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

Unique  (cost= 1312 . 62 .. 1313 . 79  rows= 117  width= 376 ) (actual time= 7 . 385 .. 7 . 684  rows= 29  loops= 1 )
  ->  Sort  (cost= 1312 . 62 .. 1312 . 91  rows= 117  width= 376 ) (actual time= 7 . 381 .. 7 . 479  rows= 59  loops= 1 )
        Sort Key: tths.count, tths.id, files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Nested Loop  (cost= 1236 . 98 .. 1308 . 60  rows= 117  width= 376 ) (actual time= 4 . 686 .. 6 . 903  rows= 59  loops= 1 )
              Join Filter: (services.type = service.id)
              ->  Index Scan using service_pkey on service  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 13 ) (actual time= 0 . 014 .. 0 . 017  rows= 1  loops= 1 )
              ->  Merge Join  (cost= 1236 . 98 .. 1298 . 87  rows= 117  width= 371 ) (actual time= 4 . 660 .. 6 . 652  rows= 59  loops= 1 )
                    Merge Cond: (services.server = servers.id)
                    ->  Sort  (cost= 1236 . 98 .. 1237 . 28  rows= 117  width= 368 ) (actual time= 4 . 601 .. 4 . 701  rows= 59  loops= 1 )
                          Sort Key: services.server
                          Sort Method:  quicksort  Memory: 55kB
                          ->  Merge Join  (cost= 1179 . 28 .. 1232 . 96  rows= 117  width= 368 ) (actual time= 2 . 522 .. 4 . 431  rows= 59  loops= 1 )
                                Merge Cond: (services.id = folders.service)
                                ->  Index Scan using services_pkey on services  (cost= 0 . 00 .. 51 . 28  rows= 414  width= 23 ) (actual time= 0 . 016 .. 0 . 824  rows= 413  loops= 1 )
                                ->  Sort  (cost= 1179 . 15 .. 1179 . 44  rows= 117  width= 353 ) (actual time= 2 . 459 .. 2 . 569  rows= 59  loops= 1 )
                                      Sort Key: folders.service
                                      Sort Method:  quicksort  Memory: 55kB
                                      ->  Nested Loop  (cost= 0 . 00 .. 1175 . 13  rows= 117  width= 353 ) (actual time= 0 . 390 .. 2 . 280  rows= 59  loops= 1 )
                                            ->  Nested Loop  (cost= 0 . 00 .. 714 . 17  rows= 117  width= 290 ) (actual time= 0 . 371 .. 1 . 284  rows= 59  loops= 1 )
                                                  ->  Nested Loop  (cost= 0 . 00 .. 176 . 28  rows= 20  width= 12 ) (actual time= 0 . 354 .. 0 . 718  rows= 19  loops= 1 )
                                                        ->  Function Scan on tth_search  (cost= 0 . 00 .. 5 . 20  rows= 20  width= 4 ) (actual time= 0 . 330 .. 0 . 362  rows= 20  loops= 1 )
                                                        ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 54  rows= 1  width= 8 ) (actual time= 0 . 009 .. 0 . 011  rows= 1  loops= 20 )
                                                              Index Cond: (tths.id = tth_search.tth_search)
                                                  ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 26 . 82  rows= 6  width= 286 ) (actual time= 0 . 008 .. 0 . 015  rows= 3  loops= 19 )
                                                        Index Cond: (files.tth = tths.id)
                                            ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 3 . 93  rows= 1  width= 75 ) (actual time= 0 . 008 .. 0 . 010  rows= 1  loops= 59 )
                                                  Index Cond: (folders.id = files.path)
                    ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 59 . 13  rows= 402  width= 11 ) (actual time= 0 . 016 .. 0 . 790  rows= 410  loops= 1 )
Total runtime:  7 . 924  ms

...

Рейтинг:

0 / 0

29.07.2008, 13:51

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457923

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKYПосле долгих перезапусков выборки удалось получить вот такую вот картину (:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.

->  Merge Join  (cost= 1236 . 98 .. 1298 . 87  rows= 117  width= 371 ) (actual time= 4 . 660 .. 6 . 652  rows= 59  loops= 1 )
    Merge Cond: (services.server = servers.id)
    ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 59 . 13  rows= 402  width= 11 ) (actual time= 0 . 016 .. 0 . 790  rows= 410  loops= 1 )

->  Merge Join  (cost= 1179 . 28 .. 1232 . 96  rows= 117  width= 368 ) (actual time= 2 . 522 .. 4 . 431  rows= 59  loops= 1 )
    Merge Cond: (services.id = folders.service)
    ->  Index Scan using services_pkey on services  (cost= 0 . 00 .. 51 . 28  rows= 414  width= 23 ) (actual time= 0 . 016 .. 0 . 824  rows= 413  loops= 1 )

здесь может немного быстрее оказаться NestedLoop.

попробуйте после трех команд:
set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;

...

Рейтинг:

0 / 0

29.07.2008, 14:19

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457964

UKY

Гость

Я просто в шоке:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

Unique  (cost= 1680 . 72 .. 1681 . 89  rows= 117  width= 376 ) (actual time= 4 . 654 .. 4 . 930  rows= 29  loops= 1 )
  ->  Sort  (cost= 1680 . 72 .. 1681 . 01  rows= 117  width= 376 ) (actual time= 4 . 649 .. 4 . 747  rows= 59  loops= 1 )
        Sort Key: tths.count, tths.id, files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Nested Loop  (cost= 0 . 00 .. 1676 . 70  rows= 117  width= 376 ) (actual time= 0 . 426 .. 4 . 104  rows= 59  loops= 1 )
              ->  Nested Loop  (cost= 0 . 00 .. 1521 . 84  rows= 117  width= 373 ) (actual time= 0 . 414 .. 3 . 346  rows= 59  loops= 1 )
                    Join Filter: (services.type = service.id)
                    ->  Index Scan using service_pkey on service  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 13 ) (actual time= 0 . 010 .. 0 . 012  rows= 1  loops= 1 )
                    ->  Nested Loop  (cost= 0 . 00 .. 1512 . 11  rows= 117  width= 368 ) (actual time= 0 . 395 .. 3 . 111  rows= 59  loops= 1 )
                          ->  Nested Loop  (cost= 0 . 00 .. 1175 . 13  rows= 117  width= 353 ) (actual time= 0 . 379 .. 2 . 281  rows= 59  loops= 1 )
                                ->  Nested Loop  (cost= 0 . 00 .. 714 . 17  rows= 117  width= 290 ) (actual time= 0 . 360 .. 1 . 278  rows= 59  loops= 1 )
                                      ->  Nested Loop  (cost= 0 . 00 .. 176 . 28  rows= 20  width= 12 ) (actual time= 0 . 344 .. 0 . 705  rows= 19  loops= 1 )
                                            ->  Function Scan on tth_search  (cost= 0 . 00 .. 5 . 20  rows= 20  width= 4 ) (actual time= 0 . 320 .. 0 . 354  rows= 20  loops= 1 )
                                            ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 54  rows= 1  width= 8 ) (actual time= 0 . 008 .. 0 . 010  rows= 1  loops= 20 )
                                                  Index Cond: (tths.id = tth_search.tth_search)
                                      ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 26 . 82  rows= 6  width= 286 ) (actual time= 0 . 008 .. 0 . 015  rows= 3  loops= 19 )
                                            Index Cond: (files.tth = tths.id)
                                ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 3 . 93  rows= 1  width= 75 ) (actual time= 0 . 008 .. 0 . 010  rows= 1  loops= 59 )
                                      Index Cond: (folders.id = files.path)
                          ->  Index Scan using services_pkey on services  (cost= 0 . 00 .. 2 . 87  rows= 1  width= 23 ) (actual time= 0 . 004 .. 0 . 006  rows= 1  loops= 59 )
                                Index Cond: (services.id = folders.service)
              ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 1 . 31  rows= 1  width= 11 ) (actual time= 0 . 003 .. 0 . 006  rows= 1  loops= 59 )
                    Index Cond: (servers.id = services.server)
Total runtime:  5 . 160  ms

...

Рейтинг:

0 / 0

29.07.2008, 14:32

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35457995

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKYЯ просто в шоке:

Код: plaintext

Total runtime:  5 . 160  ms

да, с NestedLoop получилось быстрее. но если не хочется перед каждым запросом выставлять три set enable_*, а по завершении запроса возвращать их обратно во избежание поломки планов других запросов. кстати, на передачу и выполнение этих шести команд также требуется время. то может быть проще оставить без set enable_* - 9.985 ms приемлемое время. можно еще поиграться с глобальными настройками random_page_cost и другими, на форуме обсуждалось: поиск

...

Рейтинг:

0 / 0

29.07.2008, 14:42

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#35458031

UKY

Гость

Я приводил результаты отдельно от комманд set enable_*. Меня это время выборки более чем устраивает :)

Огромное спасибо за помощь.

...

Рейтинг:

0 / 0

29.07.2008, 14:52

| Ответить | Цитировать | Написать

Период между сообщениями больше года.

Оптимизация большого запроса возможна?

#39826286

PWW

Участник

Откуда: Москва

Сообщения: 3 328

Рейтинг: 0 / 0

Прошло 10 лет, а чудо осталось чудом

set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;

Ускорение с 7 мин до 3 сек.

...

Рейтинг:

0 / 0

13.06.2019, 17:44

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#39826317

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

PWWПрошло 10 лет, а чудо осталось чудом

set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;

Ускорение с 7 мин до 3 сек.

Это значит
1)сильно более вероятно - неверно (для вашего сервера и данных) выставлены random_page_cost/seq_page_cost и effective_cache_size
или
2)планировщик сильно в селективности ошибается

...

Рейтинг:

0 / 0

13.06.2019, 19:00

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#39826323

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Maxim BogukPWWПрошло 10 лет, а чудо осталось чудом

set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;

Ускорение с 7 мин до 3 сек.

Это значит
1)сильно более вероятно - неверно (для вашего сервера и данных) выставлены random_page_cost/seq_page_cost и effective_cache_size
или
2)планировщик сильно в селективности ошибается

не-а
планировщик пж и на одной таблице бывает лажает. а уж на джойнах как правило.

т.ч. я бы предположил в корне неверную мат модель рассчёта костов. оно гораздо более похоже на правду.

...

Рейтинг:

0 / 0

13.06.2019, 19:27

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#39826365

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

qwwqт.ч. я бы предположил в корне неверную мат модель рассчёта костов. оно гораздо более похоже на правду.

А можете предложить корректную модель?
Ну или может есть ссылки на публикации по этой теме?

...

Рейтинг:

0 / 0

13.06.2019, 23:49

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#39826404

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

vyegorovqwwqт.ч. я бы предположил в корне неверную мат модель рассчёта костов. оно гораздо более похоже на правду.

А можете предложить корректную модель?
Ну или может есть ссылки на публикации по этой теме?
как бы я высказал предположение, следующее из наблюдений

а насчёт публикаций -- это общее место для практикующих пж--кодеров
21902781 -- последняя
искать лениво , но запинывание кривожоптимизатора на 2--3 порядка на нужный план малоосмысленными довесками в where -- и всякое прочее принуждение к нужному индексу -- стандартное занятие. у прошлом годе на хабре было и тут упоминалось. искать просто лень. как правило ваша команда отмалчивается в тряпочку или бухтит невнятно. а я ищи потом.

не говоря о том, что оно из коробки многого не умеет, что руками всё время приходится реализовывать. чтонть на тему проходов сиком по индексу -- из разряда луз-сканоподобных переборов. что скучно крайне и озлобляет. ну и т.п.

...

Рейтинг:

0 / 0

14.06.2019, 08:48

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#39826539

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

vyegorov,

к тому же искать не всегда удобно. то ли кто-то стыдливо подчищает . то ли случайно оно пропадает с наших экранов. но лежало там примерно это

нашёл по старым следам 21025942

а в пердыдущей ссылке сваливание на нормальный план при оффсете вообще ничем кроме отвала башки у пж не объясняется -- там попросту кост с оффсетом меньше коста без оного. чего не может быть никогда. ни при какой непротиворечивой системе оценки костов. т.е. внутре ПеЖе -- рыбу заворачивали. и, видимо, на каждой страничке кода. и много раз.

не удивлюсь, если кто--то годами приторговывает патчами, тщательно оберегаемыми от коммита в ".." кактам у вас принято -- эээ...-- "ванильную" версию.

...

Рейтинг:

0 / 0

14.06.2019, 12:33

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#39827153

PWW

Участник

Откуда: Москва

Сообщения: 3 328

Рейтинг: 0 / 0

Вот, что я понял из знакомства с Postgres:

Postgres рабочий сервер для не ответственных проектов.

Планер запросов в Postgres не работает , все надо указывать через хинты. Примеры хинтов https://habr.com/ru/post/169751/.
После прямого указания хинтами какими индексами запрос должен воспользоваться, запрос выполняется за 200мс, без него он ... выполняется... он ..., снял его через 20минут ожидания.

Установки которые я опробовал ранее топорны
set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;

На одном запросе они работают, на другом похожем запросе который работает быстро и без этих set enable_..., если их поставить, то скорость катастрофически падает (зачесалось еще ускорить).

Уж лучше хинты
/*+ IndexScan(PK_t_key_id_key IX_Info_page_key_www_id_key IX_Info_page_key_www_id_page) */
SELECT * FROM t_key INNER JOIN ...

В данном проекте у меня 3 запроса, 5 статических таблиц на 99,8Гб с индексами, один пользователь и никакой ответственности. Три запроса я могу вылизать. Но я не могу себе представить как можно ухаживать за проектом с >1000 таблиц и >10'000 запросов, планы которых будут протухать по мере добавления данных. Возможно в Postgres еще много разных фичей. К счастью, пока они мне не известны, поэтому меня все устраивает.

Бесплатный сыр не только в мышеловке, но и в GNU.

...

Рейтинг:

0 / 0

17.06.2019, 11:34

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#39827157

PWW

Участник

Откуда: Москва

Сообщения: 3 328

Рейтинг: 0 / 0

Дополню - если открыть новую вкладке для запроса в pgAdmin и выполнить запрос с хинтами , то он походит по времени, сели хотя бы один раз запустить запрос с set ..., то после этого запрос выполняется с хинтами без set ... .

Мой вопрос конечно решен, но планера запросов просто нет, зато есть шикарный бубен для плясок.

...

Рейтинг:

0 / 0

17.06.2019, 11:53

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#39827175

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

PWWДополню - если открыть новую вкладке для запроса в pgAdmin и выполнить запрос с хинтами , то он походит по времени, сели хотя бы один раз запустить запрос с set ..., то после этого запрос выполняется с хинтами без set ... .

Мой вопрос конечно решен, но планера запросов просто нет, зато есть шикарный бубен для плясок.
blahblahblah

если бы вместо блаблабла был приведен конкретный запрос с експлейнами (даже без аналайза буфферсов и т.п.) вам было бы немного больше веры . и внимания с пониманием.

а пресловутый бубен не используют "не только лишь все".

ибо "стандартных" бубенизаций без этого "хинтования" известно довольно много. накоплено, т.с. непосильным трудом.

в пж все ж таки есть планировщик, правда, как это видно по планам по ссылкам, ведёт он себя во многих случаях как больной на всю голову двоешник. вот эти случаи бы поподробнее перечислить и проанализировать -- на предмет поиска дыр в реализации и\или логике. ссылки на конкретные случаи абсолютной неадекватности планера я выше давал.

к тому же алгоритмически планер беден до невозможности. -- например при наличии индексов по (А) и (Б) запрос вида "выбрать 20 записей вдоль индекса А досортированных по уникализирующему суффиксу Б " (select * from t order by A,B limit 20) он будет не через "взять не менее 20 вдоль А -- до следующего (по отношению к 20-му значению выборки) узла индекса А, результирующую выборку пересортировать с учетом Б и обрезать 20 первых" . а тупо сканом по Б в лучшем случае. или фуллсканом. и сиди, придумывай всякий раз, как это угрёбище заставить делать по--людски: 20794074

...

Рейтинг:

0 / 0

17.06.2019, 12:31

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#39827188

PWW

Участник

Откуда: Москва

Сообщения: 3 328

Рейтинг: 0 / 0

qwwq,

Описано поведение именно этого запроса.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;
--set enable_seqscan to on;
--set enable_hashjoin to on;
--set enable_mergejoin to on;
explain
/*+ IndexScan(PK_t_key_id_key IX_Info_page_key_www_id_key IX_Info_page_key_www_id_page) */
SELECT distinct t_key.keys, t_key.id_key,
                         t_key_1.keys AS Expr3, t_key_1.ch_t
FROM t_key INNER JOIN
                         t_info_page_key ON t_key.id_key = t_info_page_key.id_key INNER JOIN
                         t_info_page_key AS t_info_page_key_1 ON t_info_page_key.id_page = t_info_page_key_1.id_page INNER JOIN
                         t_key AS t_key_1 ON t_info_page_key_1.id_key = t_key_1.id_key
WHERE (t_key.keys ilike '%e110%') AND (t_info_page_key.pos_y > 20) AND (t_info_page_key_1.pos_y > 20)

В итоге его надо всеравно запускать с set... хотябы первый раз (открытая вкладка по видимому держит сессию, поэтому второй раз можно и без set...)

План
"Unique (cost=120459744.80..120735990.51 rows=22099657 width=104)"
" -> Sort (cost=120459744.80..120514993.94 rows=22099657 width=104)"
" Sort Key: t_key.keys, t_key.id_key, t_key_1.keys, t_key_1.ch_t"
" -> Gather (cost=1090.03..114138126.61 rows=22099657 width=104)"
" Workers Planned: 2"
" -> Nested Loop (cost=90.03..111927160.91 rows=9208190 width=104)"
" -> Nested Loop (cost=89.46..39331434.90 rows=9208190 width=56)"
" -> Nested Loop (cost=88.89..107354.46 rows=7283 width=56)"
" -> Parallel Bitmap Heap Scan on t_key (cost=88.32..21369.13 rows=2383 width=52)"
" Recheck Cond: ((keys)::text ~~* '%e110%'::text)"
" -> Bitmap Index Scan on trgm_t_key_idx (cost=0.00..86.89 rows=5719 width=0)"
" Index Cond: ((keys)::text ~~* '%e110%'::text)"
" -> Index Scan using "IX_Info_page_key_www_id_key" on t_info_page_key (cost=0.57..36.03 rows=5 width=8)"
" Index Cond: (id_key = t_key.id_key)"
" Filter: (pos_y > 20)"
" -> Index Scan using "IX_Info_page_key_www_id_page" on t_info_page_key t_info_page_key_1 (cost=0.57..5376.55 rows=915 width=8)"
" Index Cond: (id_page = t_info_page_key.id_page)"
" Filter: (pos_y > 20)"
" -> Index Scan using "PK_t_key_id_key" on t_key t_key_1 (cost=0.56..7.88 rows=1 width=56)"
" Index Cond: (id_key = t_info_page_key_1.id_key)"

Следующий запрос выполняется без плясок. Только что проверил, он выполняется с любыми set... on/off.

Код: sql

1.
2.
3.
4.
5.
6.

SELECT         t_page.page_, U1.u, t_key.keys, t_info_page_key.pos_y, t_info_page_key.pos_g
FROM            U1 INNER JOIN
                         t_page ON U1.k = t_page.id_www INNER JOIN
                         t_info_page_key ON t_page.id_page = t_info_page_key.id_page INNER JOIN
                         t_key ON t_info_page_key.id_key = t_key.id_key
WHERE        (t_page.page_ iLIKE '%E110%') AND   (t_info_page_key.pos_y > 20) AND (t_info_page_key.pos_y > 20)

План
"Nested Loop (cost=531.24..1266476.13 rows=100 width=214)"
" -> Nested Loop (cost=530.67..1265721.81 rows=100 width=170)"
" -> Nested Loop (cost=530.10..1088286.28 rows=34 width=162)"
" -> Seq Scan on u1 (cost=0.00..1.12 rows=12 width=122)"
" -> Bitmap Heap Scan on t_page (cost=530.10..90690.40 rows=3 width=48)"
" Recheck Cond: (id_www = u1.k)"
" Filter: ((page_)::text ~~* '%E110%'::text)"
" -> Bitmap Index Scan on t_page_id_www_idx (cost=0.00..530.10 rows=28560 width=0)"
" Index Cond: (id_www = u1.k)"
" -> Index Scan using "IX_Info_page_key_www_id_page" on t_info_page_key (cost=0.57..5209.54 rows=915 width=16)"
" Index Cond: (id_page = t_page.id_page)"
" Filter: ((pos_y > 20) AND (pos_y > 20))"
" -> Index Scan using "PK_t_key_id_key" on t_key (cost=0.56..7.54 rows=1 width=52)"
" Index Cond: (id_key = t_info_page_key.id_key)"

...

Рейтинг:

0 / 0

17.06.2019, 12:57

| Ответить | Цитировать | Написать

Оптимизация большого запроса возможна?

#39827237

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

PWW,

поскольку вы принуждаете к нестед--лупу, то вместо "сет--енейблов" можно попытаться воспользоваться лейтерал--джойнами. везде или нет -- зависит от мощностей связок (где то могут остаться более выгодными хеш -- джойны)

примерно в таком направлении

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

SELECT distinct
	t_key.keys
	,t_key.id_key
	,t_key_1.keys AS Expr3
	,t_key_1.ch_t
FROM t_key
cross JOIN lateral
	(select * from t_info_page_key
	where  t_key.id_key = t_info_page_key.id_key
		AND (t_info_page_key.pos_y > 20)
	--order by id_key,pos_y... --index to scan (id_key,pos_y)
	limit 1000000000 -- prinuzhdenie k nested--loop-s
	) t_info_page_key
cross JOIN lateral
	(select * from t_info_page_key AS t_info_page_key_1
	where t_info_page_key.id_page = t_info_page_key_1.id_page
		AND (t_info_page_key_1.pos_y > 20)
	--order by id_page,pos_y --index to scan (id_page,pos_y)
	limit 1000000000
	) as t_info_page_key_1
cross JOIN lateral
	(select * from t_key AS t_key_1
	where t_info_page_key_1.id_key = t_key_1.id_key
	--order by id_key[,keys,ch_t]... --index to scan
	limit 1000000000
	) t_key_1
WHERE t_key.keys ilike '%e110%'

особо хорошо работает, если вы заведомо знаете что мощность или строго 1 (limit 1) или не выше небольшого числа (limit 7--8).

я правильно понимаю, что это у вас какие-то полнотекстовые самопалы ?

...

Рейтинг:

0 / 0

17.06.2019, 14:27

| Ответить | Цитировать | Написать

65 сообщений из 65, показаны все 3 страниц

все

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация большого запроса возможна?

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?all=1&fid=53&tid=1995161]:	0ms
get settings:	10ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	53ms
get topic data:	12ms
get forum data:	3ms
get page messages:	90ms
get tp. blocked users:	1ms
others:	26ms

total:	218ms