Re: Very slow Query compared to Oracle / SQL - Server

Sorry if I'm cumulatively answering everyone in one E-Mail, I'm not sure
how I'm supposed to do it. (single E-Mails vs many)

> Can you try tuning by increasing the shared_buffers slowly in steps of
> 500MB, and running explain analyze against the query.

-- 2500 MB shared buffers - random_page_cost = 1;
Gather Merge (cost=343085.23..392186.19 rows=420836 width=2542) (actual
time=2076.329..3737.050 rows=516517 loops=1)
Output: column1, .. , column54
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=295446
-> Sort (cost=342085.21..342611.25 rows=210418 width=2542) (actual
time=2007.487..2202.707 rows=172172 loops=3)
Output: column1, .. , column54
Sort Key: logtable.timestampcol DESC
Sort Method: quicksort Memory: 65154kB
Worker 0: Sort Method: quicksort Memory: 55707kB
Worker 1: Sort Method: quicksort Memory: 55304kB
Buffers: shared hit=295446
Worker 0: actual time=1963.969..2156.624 rows=161205 loops=1
Buffers: shared hit=91028
Worker 1: actual time=1984.700..2179.697 rows=161935 loops=1
Buffers: shared hit=92133
-> Parallel Bitmap Heap Scan on schema.logtable
(cost=5546.39..323481.21 rows=210418 width=2542) (actual
time=322.125..1618.971 rows=172172 loops=3)
Output: column1, .. , column54
Recheck Cond: ((logtable.entrytype = 4000) OR
(logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
Filter: (logtable.archivestatus <= 1)
Heap Blocks: exact=110951
Buffers: shared hit=295432
Worker 0: actual time=282.201..1595.117 rows=161205 loops=1
Buffers: shared hit=91021
Worker 1: actual time=303.671..1623.299 rows=161935 loops=1
Buffers: shared hit=92126
-> BitmapOr (cost=5546.39..5546.39 rows=510578 width=0)
(actual time=199.119..199.119 rows=0 loops=1)
Buffers: shared hit=1334
-> Bitmap Index Scan on idx_entrytype
(cost=0.00..682.13 rows=67293 width=0) (actual time=28.856..28.857
rows=65970 loops=1)
Index Cond: (logtable.entrytype = 4000)
Buffers: shared hit=172
-> Bitmap Index Scan on idx_entrytype
(cost=0.00..2223.63 rows=219760 width=0) (actual time=108.871..108.872
rows=225283 loops=1)
Index Cond: (logtable.entrytype = 4001)
Buffers: shared hit=581
-> Bitmap Index Scan on idx_entrytype
(cost=0.00..2261.87 rows=223525 width=0) (actual time=61.377..61.377
rows=225264 loops=1)
Index Cond: (logtable.entrytype = 4002)
Buffers: shared hit=581
Settings: random_page_cost = '1', temp_buffers = '80MB', work_mem = '1GB'
Planning Time: 0.940 ms
Execution Time: 4188.083 ms
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 3000 MB shared buffers - random_page_cost = 1;
Gather Merge (cost=343085.23..392186.19 rows=420836 width=2542) (actual
time=2062.280..3763.408 rows=516517 loops=1)
Output: column1, .. , column54
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=295446
-> Sort (cost=342085.21..342611.25 rows=210418 width=2542) (actual
time=1987.933..2180.422 rows=172172 loops=3)
Output: column1, .. , column54
Sort Key: logtable.timestampcol DESC
Sort Method: quicksort Memory: 66602kB
Worker 0: Sort Method: quicksort Memory: 55149kB
Worker 1: Sort Method: quicksort Memory: 54415kB
Buffers: shared hit=295446
Worker 0: actual time=1963.059..2147.916 rows=159556 loops=1
Buffers: shared hit=89981
Worker 1: actual time=1949.726..2136.200 rows=158554 loops=1
Buffers: shared hit=90141
-> Parallel Bitmap Heap Scan on schema.logtable
(cost=5546.39..323481.21 rows=210418 width=2542) (actual
time=340.705..1603.796 rows=172172 loops=3)
Output: column1, .. , column54
Recheck Cond: ((logtable.entrytype = 4000) OR
(logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
Filter: (logtable.archivestatus <= 1)
Heap Blocks: exact=113990
Buffers: shared hit=295432
Worker 0: actual time=317.918..1605.548 rows=159556 loops=1
Buffers: shared hit=89974
Worker 1: actual time=304.744..1589.221 rows=158554 loops=1
Buffers: shared hit=90134
-> BitmapOr (cost=5546.39..5546.39 rows=510578 width=0)
(actual time=218.972..218.973 rows=0 loops=1)
Buffers: shared hit=1334
-> Bitmap Index Scan on idx_entrytype
(cost=0.00..682.13 rows=67293 width=0) (actual time=37.741..37.742
rows=65970 loops=1)
Index Cond: (logtable.entrytype = 4000)
Buffers: shared hit=172
-> Bitmap Index Scan on idx_entrytype
(cost=0.00..2223.63 rows=219760 width=0) (actual time=119.120..119.121
rows=225283 loops=1)
Index Cond: (logtable.entrytype = 4001)
Buffers: shared hit=581
-> Bitmap Index Scan on idx_entrytype
(cost=0.00..2261.87 rows=223525 width=0) (actual time=62.097..62.098
rows=225264 loops=1)
Index Cond: (logtable.entrytype = 4002)
Buffers: shared hit=581
Settings: random_page_cost = '1', temp_buffers = '80MB', work_mem = '1GB'
Planning Time: 2.717 ms
Execution Time: 4224.670 ms
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 3500 MB shared buffers - random_page_cost = 1;
Gather Merge (cost=343085.23..392186.19 rows=420836 width=2542) (actual
time=3578.155..4932.858 rows=516517 loops=1)
Output: column1, .. , column54
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=14 read=295432 written=67
-> Sort (cost=342085.21..342611.25 rows=210418 width=2542) (actual
time=3482.159..3677.227 rows=172172 loops=3)
Output: column1, .. , column54
Sort Key: logtable.timestampcol DESC
Sort Method: quicksort Memory: 58533kB
Worker 0: Sort Method: quicksort Memory: 56878kB
Worker 1: Sort Method: quicksort Memory: 60755kB
Buffers: shared hit=14 read=295432 written=67
Worker 0: actual time=3435.131..3632.985 rows=166842 loops=1
Buffers: shared hit=7 read=95783 written=25
Worker 1: actual time=3441.545..3649.345 rows=179354 loops=1
Buffers: shared hit=5 read=101608 written=20
-> Parallel Bitmap Heap Scan on schema.logtable
(cost=5546.39..323481.21 rows=210418 width=2542) (actual
time=345.111..3042.932 rows=172172 loops=3)
Output: column1, .. , column54
Recheck Cond: ((logtable.entrytype = 4000) OR
(logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
Filter: (logtable.archivestatus <= 1)
Heap Blocks: exact=96709
Buffers: shared hit=2 read=295430 written=67
Worker 0: actual time=300.525..2999.403 rows=166842 loops=1
Buffers: shared read=95783 written=25
Worker 1: actual time=300.552..3004.859 rows=179354 loops=1
Buffers: shared read=101606 written=20
-> BitmapOr (cost=5546.39..5546.39 rows=510578 width=0)
(actual time=241.996..241.997 rows=0 loops=1)
Buffers: shared hit=2 read=1332
-> Bitmap Index Scan on idx_entrytype
(cost=0.00..682.13 rows=67293 width=0) (actual time=37.129..37.130
rows=65970 loops=1)
Index Cond: (logtable.entrytype = 4000)
Buffers: shared read=172
-> Bitmap Index Scan on idx_entrytype
(cost=0.00..2223.63 rows=219760 width=0) (actual time=131.051..131.052
rows=225283 loops=1)
Index Cond: (logtable.entrytype = 4001)
Buffers: shared hit=1 read=580
-> Bitmap Index Scan on idx_entrytype
(cost=0.00..2261.87 rows=223525 width=0) (actual time=73.800..73.800
rows=225264 loops=1)
Index Cond: (logtable.entrytype = 4002)
Buffers: shared hit=1 read=580
Settings: random_page_cost = '1', temp_buffers = '80MB', work_mem = '1GB'
Planning Time: 0.597 ms
Execution Time: 5389.811 ms

This doesn't seem to have had an effect.
Thanks for the suggestion.

Have you try of excluding not null from index? Can you give dispersion of
> archivestatus?
>

Yes I have, it yielded the same performance boost as :

create index test on logtable(entrytype) where archivestatus <= 1;

I wonder what the old query plan was...
> Would you include links to your prior correspondance ?

So prior Execution Plans are present in the SO.
The other forums I've tried are the official slack channel :
https://postgresteam.slack.com/archives/C0FS3UTAP/p1620286295228600
And SO :
https://stackoverflow.com/questions/67401792/slow-running-postgresql-query
But I think most of the points discussed in these posts have already been
mentionend by you except bloating of indexes.

Oracle is apparently doing a single scan on "entrytype".
> As a test, you could try forcing that, like:
> begin; SET enable_bitmapscan=off ; explain (analyze) [...]; rollback;
> or
> begin; DROP INDEX idx_arcstatus; explain (analyze) [...]; rollback;

I've tried enable_bitmapscan=off but it didn't yield any good results.

-- 2000 MB shared buffers - random_page_cost = 4 - enable_bitmapscan to off
Gather Merge (cost=543949.72..593050.69 rows=420836 width=2542) (actual
time=7716.031..9043.399 rows=516517 loops=1)
Output: column1, .., column54
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=192 read=406605
-> Sort (cost=542949.70..543475.75 rows=210418 width=2542) (actual
time=7642.666..7835.527 rows=172172 loops=3)
Output: column1, .., column54
Sort Key: logtable.timestampcol DESC
Sort Method: quicksort Memory: 58803kB
Worker 0: Sort Method: quicksort Memory: 60376kB
Worker 1: Sort Method: quicksort Memory: 56988kB
Buffers: shared hit=192 read=406605
Worker 0: actual time=7610.482..7814.905 rows=177637 loops=1
Buffers: shared hit=78 read=137826
Worker 1: actual time=7607.645..7803.561 rows=167316 loops=1
Buffers: shared hit=80 read=132672
-> Parallel Seq Scan on schema.logtable (cost=0.00..524345.70
rows=210418 width=2542) (actual time=1.669..7189.365 rows=172172 loops=3)
Output: column1, .., column54
Filter: ((logtable.acrhivestatus <= 1) AND
((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR
(logtable.entrytype = 4002)))
Rows Removed by Filter: 4533459
Buffers: shared hit=96 read=406605
Worker 0: actual time=1.537..7158.286 rows=177637 loops=1
Buffers: shared hit=30 read=137826
Worker 1: actual time=1.414..7161.670 rows=167316 loops=1
Buffers: shared hit=32 read=132672
Settings: enable_bitmapscan = 'off', temp_buffers = '80MB', work_mem = '1GB'
Planning Time: 0.725 ms
Execution Time: 9500.928 ms
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2000 MB shared buffers - random_page_cost = 4 - -- 2000 -- 2000 MB shared
buffers - random_page_cost = 1 - enable_bitmapscan to off
Gather Merge (cost=543949.72..593050.69 rows=420836 width=2542) (actual
time=7519.032..8871.433 rows=516517 loops=1)
Output: column1, .., column54
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=576 read=406221
-> Sort (cost=542949.70..543475.75 rows=210418 width=2542) (actual
time=7451.958..7649.480 rows=172172 loops=3)
Output: column1, .., column54
Sort Key: logtable.timestampcol DESC
Sort Method: quicksort Memory: 58867kB
Worker 0: Sort Method: quicksort Memory: 58510kB
Worker 1: Sort Method: quicksort Memory: 58788kB
Buffers: shared hit=576 read=406221
Worker 0: actual time=7438.271..7644.241 rows=172085 loops=1
Buffers: shared hit=203 read=135166
Worker 1: actual time=7407.574..7609.922 rows=172948 loops=1
Buffers: shared hit=202 read=135225
-> Parallel Seq Scan on schema.logtable (cost=0.00..524345.70
rows=210418 width=2542) (actual time=2.839..7017.729 rows=172172 loops=3)
Output: column1, .., column54
Filter: ((logtable.acrhivestatus <= 1) AND
((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR
(logtable.entrytype = 4002)))
Rows Removed by Filter: 4533459
Buffers: shared hit=480 read=406221
Worker 0: actual time=2.628..7006.420 rows=172085 loops=1
Buffers: shared hit=155 read=135166
Worker 1: actual time=3.948..6978.154 rows=172948 loops=1
Buffers: shared hit=154 read=135225
Settings: enable_bitmapscan = 'off', random_page_cost = '1', temp_buffers =
'80MB', work_mem = '1GB'
Planning Time: 0.621 ms
Execution Time: 9339.457 ms

Have you tune shared buffers enough? Each block is of 8k by default.
> BTW, please try to reset random_page_cost.

Look above.

I will try upgrading the minor version next.
I will also try setting up a 13.X version locally and import the data from
12.2 to 13.X and see if it might be faster.

Am Do., 6. Mai 2021 um 23:16 Uhr schrieb Imre Samu <pella(dot)samu(at)gmail(dot)com>:

> *> Postgres Version : *PostgreSQL 12.2,
> > ... ON ... USING btree
>
> IMHO:
> The next minor (bugix&security) release is near ( expected ~ May 13th,
> 2021 ) https://www.postgresql.org/developer/roadmap/
> so you can update your PostgreSQL to 12.7 ( + full Reindexing
> recommended ! )
>
> You can find a lot of B-tree index-related fixes.
> https://www.postgresql.org/docs/12/release-12-3.html Release date:
> 2020-05-14
> - Fix possible undercounting of deleted B-tree index pages in VACUUM
> VERBOSE output
> - Fix wrong bookkeeping for oldest deleted page in a B-tree index
> - Ensure INCLUDE'd columns are always removed from B-tree pivot tuples
> https://www.postgresql.org/docs/12/release-12-4.html
> - Avoid repeated marking of dead btree index entries as dead
> https://www.postgresql.org/docs/12/release-12-5.html
> - Fix failure of parallel B-tree index scans when the index condition is
> unsatisfiable
> https://www.postgresql.org/docs/12/release-12-6.html Release date:
> 2021-02-11
>
>
> > COLLATE pg_catalog."default"
>
> You can test the "C" Collation in some columns (keys ? ) ; in theory,
> it should be faster :
> "The drawback of using locales other than C or POSIX in PostgreSQL is its
> performance impact. It slows character handling and prevents ordinary
> indexes from being used by LIKE. For this reason use locales only if you
> actually need them."
> https://www.postgresql.org/docs/12/locale.html
>
> https://www.postgresql.org/message-id/flat/CAF6DVKNU0vb4ZeQQ-%3Dagg69QJU3wdjPnMYYrPYY7CKc6iOU7eQ%40mail.gmail.com
>
> Best,
> Imre
>
>
> Semen Yefimenko <semen(dot)yefimenko(at)gmail(dot)com> ezt írta (időpont: 2021. máj.
> 6., Cs, 16:38):
>
>> Hi there,
>>
>> I've recently been involved in migrating our old system to SQL Server and
>> then PostgreSQL. Everything has been working fine so far but now after
>> executing our tests on Postgres, we saw a very slow running query on a
>> large table in our database.
>> I have tried asking on other platforms but no one has been able to give
>> me a satisfying answer.
>>
>> *Postgres Version : *PostgreSQL 12.2, compiled by Visual C++ build 1914,
>> 64-bit
>> No notable errors in the Server log and the Postgres Server itself.
>>
>> The table structure :
>>
>> CREATE TABLE logtable
>> (
>> key character varying(20) COLLATE pg_catalog."default" NOT NULL,
>> id integer,
>> column3 integer,
>> column4 integer,
>> column5 integer,
>> column6 integer,
>> column7 integer,
>> column8 integer,
>> column9 character varying(128) COLLATE pg_catalog."default",
>> column10 character varying(2048) COLLATE pg_catalog."default",
>> column11 character varying(2048) COLLATE pg_catalog."default",
>> column12 character varying(2048) COLLATE pg_catalog."default",
>> column13 character varying(2048) COLLATE pg_catalog."default",
>> column14 character varying(2048) COLLATE pg_catalog."default",
>> column15 character varying(2048) COLLATE pg_catalog."default",
>> column16 character varying(2048) COLLATE pg_catalog."default",
>> column17 character varying(2048) COLLATE pg_catalog."default",
>> column18 character varying(2048) COLLATE pg_catalog."default",
>> column19 character varying(2048) COLLATE pg_catalog."default",
>> column21 character varying(256) COLLATE pg_catalog."default",
>> column22 character varying(256) COLLATE pg_catalog."default",
>> column23 character varying(256) COLLATE pg_catalog."default",
>> column24 character varying(256) COLLATE pg_catalog."default",
>> column25 character varying(256) COLLATE pg_catalog."default",
>> column26 character varying(256) COLLATE pg_catalog."default",
>> column27 character varying(256) COLLATE pg_catalog."default",
>> column28 character varying(256) COLLATE pg_catalog."default",
>> column29 character varying(256) COLLATE pg_catalog."default",
>> column30 character varying(256) COLLATE pg_catalog."default",
>> column31 character varying(256) COLLATE pg_catalog."default",
>> column32 character varying(256) COLLATE pg_catalog."default",
>> column33 character varying(256) COLLATE pg_catalog."default",
>> column34 character varying(256) COLLATE pg_catalog."default",
>> column35 character varying(256) COLLATE pg_catalog."default",
>> entrytype integer,
>> column37 bigint,
>> column38 bigint,
>> column39 bigint,
>> column40 bigint,
>> column41 bigint,
>> column42 bigint,
>> column43 bigint,
>> column44 bigint,
>> column45 bigint,
>> column46 bigint,
>> column47 character varying(128) COLLATE pg_catalog."default",
>> timestampcol timestamp without time zone,
>> column49 timestamp without time zone,
>> column50 timestamp without time zone,
>> column51 timestamp without time zone,
>> column52 timestamp without time zone,
>> archivestatus integer,
>> column54 integer,
>> column55 character varying(20) COLLATE pg_catalog."default",
>> CONSTRAINT pkey PRIMARY KEY (key)
>> USING INDEX TABLESPACE tablespace
>> )
>>
>> TABLESPACE tablespace;
>>
>> ALTER TABLE schema.logtable
>> OWNER to user;
>>
>> CREATE INDEX idx_timestampcol
>> ON schema.logtable USING btree
>> ( timestampcol ASC NULLS LAST )
>> TABLESPACE tablespace ;
>>
>> CREATE INDEX idx_test2
>> ON schema.logtable USING btree
>> ( entrytype ASC NULLS LAST)
>> TABLESPACE tablespace
>> WHERE archivestatus <= 1;
>>
>> CREATE INDEX idx_arcstatus
>> ON schema.logtable USING btree
>> ( archivestatus ASC NULLS LAST)
>> TABLESPACE tablespace;
>>
>> CREATE INDEX idx_entrytype
>> ON schema.logtable USING btree
>> ( entrytype ASC NULLS LAST)
>> TABLESPACE tablespace ;
>>
>>
>> The table contains 14.000.000 entries and has about 3.3 GB of data:
>> No triggers, inserts per day, probably 5-20 K per day.
>>
>> SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts,
>> relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE
>> relname='logtable';
>>
>> relname
>> |relpages|reltuples|relallvisible|relkind|relnatts|relhassubclass|reloptions|pg_table_size|
>>
>> ------------------|--------|---------|-------------|-------|--------|--------------|----------|-------------|
>> logtable | 405988| 14091424| 405907|r |
>> 54|false |NULL | 3326803968|
>>
>>
>> The slow running query:
>>
>> SELECT column1,..., column54 where ((entrytype = 4000 or entrytype =
>> 4001 or entrytype = 4002) and (archivestatus <= 1)) order by timestampcol
>> desc;
>>
>>
>> This query runs in about 45-60 seconds.
>> The same query runs in about 289 ms Oracle and 423 ms in SQL-Server.
>> Now I understand that actually loading all results would take a while.
>> (about 520K or so rows)
>> But that shouldn't be exactly what happens right? There should be a
>> resultset iterator which can retrieve all data but doesn't from the get go.
>>
>> With the help of some people in the slack and so thread, I've found a
>> configuration parameter which helps performance :
>>
>> set random_page_cost = 1;
>>
>> This improved performance from 45-60 s to 15-35 s. (since we are using
>> ssd's)
>> Still not acceptable but definitely an improvement.
>> Some maybe relevant system parameters:
>>
>> effective_cache_size 4GB
>> maintenance_work_mem 1GB
>> shared_buffers 2GB
>> work_mem 1GB
>>
>>
>> Currently I'm accessing the data through DbBeaver (JDBC -
>> postgresql-42.2.5.jar) and our JAVA application (JDBC -
>> postgresql-42.2.19.jar). Both use the defaultRowFetchSize=5000 to not load
>> everything into memory and limit the results.
>> The explain plan:
>>
>> EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE)...
>> (Above Query)
>>
>>
>> Gather Merge (cost=347142.71..397196.91 rows=429006 width=2558) (actual
>> time=21210.019..22319.444 rows=515841 loops=1)
>> Output: column1, .. , column54
>> Workers Planned: 2
>> Workers Launched: 2
>> Buffers: shared hit=141487 read=153489
>> -> Sort (cost=346142.69..346678.95 rows=214503 width=2558) (actual
>> time=21148.887..21297.428 rows=171947 loops=3)
>> Output: column1, .. , column54
>> Sort Key: logtable.timestampcol DESC
>> Sort Method: quicksort Memory: 62180kB
>> Worker 0: Sort Method: quicksort Memory: 56969kB
>> Worker 1: Sort Method: quicksort Memory: 56837kB
>> Buffers: shared hit=141487 read=153489
>> Worker 0: actual time=21129.973..21296.839 rows=166776 loops=1
>> Buffers: shared hit=45558 read=49514
>> Worker 1: actual time=21114.439..21268.117 rows=165896 loops=1
>> Buffers: shared hit=45104 read=49506
>> -> Parallel Bitmap Heap Scan on schema.logtable
>> (cost=5652.74..327147.77 rows=214503 width=2558) (actual
>> time=1304.813..20637.462 rows=171947 loops=3)
>> Output: column1, .. , column54
>> Recheck Cond: ((logtable.entrytype = 4000) OR
>> (logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
>> Filter: (logtable.archivestatus <= 1)
>> Heap Blocks: exact=103962
>> Buffers: shared hit=141473 read=153489
>> Worker 0: actual time=1280.472..20638.620 rows=166776
>> loops=1
>> Buffers: shared hit=45551 read=49514
>> Worker 1: actual time=1275.274..20626.219 rows=165896
>> loops=1
>> Buffers: shared hit=45097 read=49506
>> -> BitmapOr (cost=5652.74..5652.74 rows=520443 width=0)
>> (actual time=1179.438..1179.438 rows=0 loops=1)
>> Buffers: shared hit=9 read=1323
>> -> Bitmap Index Scan on idx_entrytype
>> (cost=0.00..556.61 rows=54957 width=0) (actual time=161.939..161.940
>> rows=65970 loops=1)
>> Index Cond: (logtable.entrytype = 4000)
>> Buffers: shared hit=1 read=171
>> -> Bitmap Index Scan on idx_entrytype
>> (cost=0.00..2243.22 rows=221705 width=0) (actual time=548.849..548.849
>> rows=224945 loops=1)
>> Index Cond: (logtable.entrytype = 4001)
>> Buffers: shared hit=4 read=576
>> -> Bitmap Index Scan on idx_entrytype
>> (cost=0.00..2466.80 rows=243782 width=0) (actual time=468.637..468.637
>> rows=224926 loops=1)
>> Index Cond: (logtable.entrytype = 4002)
>> Buffers: shared hit=4 read=576
>> Settings: random_page_cost = '1', search_path = '"$user", schema,
>> public', temp_buffers = '80MB', work_mem = '1GB'
>> Planning Time: 0.578 ms
>> Execution Time: 22617.351 ms
>>
>> As mentioned before, oracle does this much faster.
>>
>>
>> -------------------------------------------------------------------------------------------------------------------------
>> | Id | Operation | Name |
>> Rows | Bytes |TempSpc| Cost (%CPU)| Time |
>>
>> -------------------------------------------------------------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | |
>> 6878 | 2491K| | 2143 (1)| 00:00:01 |
>> | 1 | SORT ORDER BY | |
>> 6878 | 2491K| 3448K| 2143 (1)| 00:00:01 |
>> | 2 | INLIST ITERATOR | |
>> | | | | |
>> |* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| logtable |
>> 6878 | 2491K| | 1597 (1)| 00:00:01 |
>> |* 4 | INDEX RANGE SCAN | idx_entrytype |
>> 6878 | | | 23 (0)| 00:00:01 |
>>
>> -------------------------------------------------------------------------------------------------------------------------
>>
>> Is there much I can analyze, any information you might need to further
>> analyze this?
>>
>