Re: [PERFORM] analyzing intermediate query

tween DISTINCT and GROUP BY with respect to nulls. dokumnr is int type and is not null always. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] analyzing intermediate query

cond temporary table from first temporary table specially for this query seems to be only solution. When materialized row count will be added so that statistics is exact and select count(*) from tbl runs fast ? Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.

Re: [PERFORM] analyzing intermediate query

My list can contain 1 .. 10 records and table contains 300 records and is growing. Ah. No IN(), then ;) Temp table + ANALYZE seems your only option... In 8.3 or 8.4 I think that IN() or temp table produce exactly the same result. Andrus. -- Sent via pgsql-performance mailing

Re: [PERFORM] analyzing intermediate query

in single transaction, so is would be major appl rewrite. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] analyzing intermediate query

I noticed that query SELECT dok.* FROM dok JOIN (SELECT DISTINCT dokumnr FROM temptbl ) x USING(dokumnr); is slow in 8.1.4 I cannot use explain analyze since this query uses results from temporary table temptbl which is not available. Sometimes innter table returns only 1 row so maybe seq s

[PERFORM] Seq scan over 3.3 millions of rows instead of using date and pattern indexes

ar) OR (doktyyp = 'D'::bpchar) OR (doktyyp = 'P'::bpchar))" "-> Bitmap Index Scan on dok_kuupaev_idx (cost=0.00..1993.66 rows=347618 width=0) (actual time=97.881..97.881 rows=337770 loops=1)" " Index Cond: (kuupa

Re: [PERFORM] Increasing GROUP BY CHAR columns speed

ry is much faster. Hopefully this will not affect to result since numeric(13,8) can casted to float without data loss. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Increasing GROUP BY CHAR columns speed

why. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Increasing GROUP BY CHAR columns speed

I it seems that slowness is caused by grouping by column exchrate numeric(13,8) if this column is excluded, query takes 12 seconds if this column in present, query takes 27 (!) seconds. How to fix this ? Andrus. set search_path to firma2,public; SET work_mem = 2097151 ; explain analyze SELECT

Re: [PERFORM] Increasing GROUP BY CHAR columns speed

allowed. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Increasing GROUP BY CHAR columns speed

s allow to mark columns as C locale. I havent found this nor chartobin() function in PostgreSql. Will creating BinaryNullIfNot(dbkonto.objekt1, '+') function solve this ? Andrus. New testcase: set search_path to firma2,public; SET work_mem = 2097151; -- 9 seconds -- SET work_mem = 109

[PERFORM] Increasing GROUP BY CHAR columns speed

Group by using CHAR columns takes abnormally big time. How to speed it ? Andrus. 8.1.4, cluster locale is en-us, db encoding is utf-8 set search_path to firma2,public; explain analyze SELECT CASE WHEN bilkaib.raha='EEK' THEN 0 ELSE bilkaib.id END, bilkaib.DB, CASE WHEN dbkon

Re: [PERFORM] Increasing pattern index query speed

pchar) OR (doktyyp = 'U'::bpchar) OR (doktyyp = 'D'::bpchar) OR (doktyyp = 'P'::bpchar))" "-> Bitmap Index Scan on dok_kuupaev_idx (cost=0.00..414.75 rows=72500 width=0) (actual time=20.049..20.049 rows=72664 loops=1)&qu

Re: [PERFORM] Increasing pattern index query speed

a how to use random() to generate random products for every order. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Increasing pattern index query speed

postgresql.conf file Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Increasing pattern index query speed

LUMN toode SET STATISTICS 1000; This fixes testcase in live server, see my other message. Is it OK to run ALTER TABLE rid ALTER COLUMN toode SET STATISTICS 1000 in prod database or should I try to decrease 1000 to smaller value ? rid is big increasing table and is changed frequently, mostly

Re: [PERFORM] Increasing pattern index query speed

" "Filter: (toode ~~ '9910%'::text)" "-> Index Scan using toode_pkey on toode (cost=0.00..6.00 rows=1 width=24) (actual time=0.016..0.020 rows=1 loops=108)" " Index Cond: ("outer".toode = toode.toode)

Re: [PERFORM] Increasing pattern index query speed

the same. How to make BETWEEN query fast (real queries are running as between queries over some date range)? P.S. VACUUM issues warning that free space map 15 is not sufficient, 16 nodes reqired. Two days ago after vacuum full there were 6 used enties in FSM. No idea why this occurs.

Re: [PERFORM] limit clause produces wrong query plan

... FROM bigtable ORDER BY intprimarykey OFFSET 0 LIMIT 100 cannot be used in PostgreSql at all for big tables. Do you have any idea how to fix this ? Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Hash join on int takes 8..114 seconds

10 -5 000 S 0.0 0.0 1:36.43 xfsdatad/0 706 root 10 -5 000 S 0.0 0.0 0:00.00 kseriod 723 root 13 -5 000 S 0.0 0.0 0:00.00 kpsmoused 738 root 11 -5 000 S 0.0 0.0 0:00.00 ata/0 740 root 11 -5 00

Re: [PERFORM] Increasing pattern index query speed

ingle product starting with 9910 So both queries should scan exactly same numbers of rows. Can I give you some wider-ranging suggestions Andrus? 1. Fix the vacuuming issue in your hash-join question. I have ran VACUUM FULL VERBOSE ANALYSE and set max_fsm_pages=15 So issue is fixed before

Re: [PERFORM] limit clause produces wrong query plan

rows in table. This appoarch requires updating x in every row in big table after each insert, delete or order column change and is thus extremely slow. So I do'nt understand how this can be used for large tables. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgres

Re: [PERFORM] Hash join on int takes 8..114 seconds

ted tables and / or slow I/O subsystem, moving to a similar system won't help I guess. I have ran VACUUM FULL ANALYZE VERBOSE and set MAX_FSM_PAGES = 15 So there is no any bloat except pg_shdepend indexes which should not affect to query speed. Andrus. -- Sent via pgsql-performan

Re: [PERFORM] Hash join on int takes 8..114 seconds

sk I/O in your use cases, there are generally several things that can be done to tune Linux I/O. The main ones in my experience are the 'readahead' value for each disk which helps sequential reads significantly, and trying out the linux 'deadline' scheduler and comparing it to

Re: [PERFORM] Hash join on int takes 8..114 seconds

cloning script from pgadmin command window. Only way I found is to use SSH with pg_dup/pg_restore. This requires SSH access to server and SSH port opening to public internet. Or probably try to run CLUSTER command in prod server. Hopefully clustering by product id cannot make things slow to

[PERFORM] limit clause produces wrong query plan

=1)" "Index Cond: (doktyyp = 'J'::bpchar)" "Total runtime: 0.245 ms" How to fix this without dropping dok_doktyyp index so that limit can safely used for paged data access ? indexes: dok_doktyyp: dok(doktyyp) dok_dokumnr_idx: dok(dokumnr) types: doku

Re: [PERFORM] Hash join on int takes 8..114 seconds

ance: 6. Upgrade to 8.4 or to 8.3.5 7. run server on standalone mode and recover 1 GB pg_shdepend bloated index. 8. tune some conf file parameters: work_mem = 512 I'd consider increasing this value a little - 0.5 MB seems too low to me (but not necessarily). effective_cache_size= 7 Well

Re: [PERFORM] seq scan over 3.3 million rows instead of single keyindex access

An index-scan makes only sense if rid contains considerable more than 300 rows. I'm sorry, I meant using index to get the row. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mai

Re: [PERFORM] seq scan over 3.3 million rows instead of single key index access

running query ? Andrus. set search_path to firma2,public; CREATE TEMP TABLE idtellUued(dokumnr INT) ON COMMIT DROP; INSERT INTO idtellUued VALUES(1249228); analyze idtelluued; explain analyze select 1 from dok JOIN rid USING(dokumnr) JOIN idtellUued USING(dokumnr) "Nested Loop (cost

[PERFORM] Increasing pattern index query speed

Both queries return same result (19) and return same data. Pattern query is a much slower (93 sec) than equality check (13 sec). How to fix this ? Using 8.1.4, utf-8 encoding, et-EE locale. Andrus. SELECT sum(1) FROM dok JOIN rid USING (dokumnr) JOIN toode USING (toode) WHERE rid.toode

[PERFORM] seq scan over 3.3 million rows instead of single key index access

There are indexes on rid(dokumnr) and dok(dokumnr) and dokumnr is int. Instead of using single key index, 8.1.4 scans over whole rid table. Sometimes idtelluued can contain more than single row so replacing join with equality is not possible. How to fix ? Andrus. CREATE TEMP TABLE

Re: [PERFORM] Hash join on int takes 8..114 seconds

3 (or 1..6 ) random products? I tried to use random row sample from http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks-i but in this case constant product is returned always. It seems than query containing randon() is executed only once. Andrus. begin; CREATE TEMP TABLE orders (order_id INTE

Re: [PERFORM] Hash join on int takes 8..114 seconds

1234 MB 6 1232 pg_catalog.pg_shdepend_depender_index 795 MB 7 1233 pg_catalog.pg_shdepend_reference_index439 MB Andrus. vacuum full verbose pg_shdepend; INFO: vacuuming "pg_catalog.pg_shdepend" INFO: "pg_shdepend": found 254 rem

Re: [PERFORM] Hash join on int takes 8..114 seconds

rbage messages below. So I hope that vacuum is running well, isn't it ? Andrus. 2008-11-19 00:00:48 EET11728 1 LOG: autovacuum: processing database "postgres" 2008-11-19 00:01:48 EET11729 1 LOG: autovacuum: processing database "mydb1" 2008-11-19 00:02:48 EE

Re: [PERFORM] Hash join on int takes 8..114 seconds

k_mem = 131072 fsync = on effective_cache_size= 7 log_min_duration_statement= 30000 Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hash join on int takes 8..114 seconds

. I do'nt understand how autovacuum can avoid this: it does not perform vacuum full so pg_shdepend ja my tables become bloated again and again. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [PERFORM] Hash join on int takes 8..114 seconds

ctId LIKE :p1 || '%' AND dok.SaleDate>=:p2 :p1 and :p2 are parameters different for different queries. dok contains several years of data. :p2 is usually only few previous months or last year ago. SELECT column list contains fixed list of known columns from all tables. How to crea

Re: [PERFORM] Hash join on int takes 8..114 seconds

used to access this db) or is only solution to manully run vacuum full pg_shdepend reindex pg_shdepend periodically ? How to vacuum full pg_shdepend automatically so that other users can work at same time ? Hopefully this table size does not affect to query speed. Andrus. -- Sent via pgsq

Re: [PERFORM] Hash join on int takes 8..114 seconds

EY (toode) REFERENCES firma2.toode (toode) ) -- Products TOODE ( toode CHAR(20) PRIMARY KEY ) To make this type of query faster I would tend to think about : - denormalization (ie adding a column in one of your tables and a multicolumn index) For this query it is possible to dupli

Re: [PERFORM] Hash join on int takes 8..114 seconds

0 100 0 Here you're stuck waiting for disks (91.0% wa). Check out vmstat and iostat to see what's happening. typing iostat returns bash: iostat: command not found It seems that this is not installed in this gentoo. Andrus. -- Sent via pgsql-performance mailing list (pgsql-

Re: [PERFORM] Hash join on int takes 8..114 seconds

_eh_3 762 root 10 -5 000 S 0.0 0.0 0:17.54 xfsbufd 763 root 10 -5 000 S 0.0 0.0 0:00.68 xfssyncd 963 root 16 -4 1712 528 336 S 0.0 0.0 0:00.24 udevd 6677 root 15 0 1728 572 400 S 0.0 0.0 0:04.99 syslog-ng Andrus. -- Sen

Re: [PERFORM] Hash join on int takes 8..114 seconds

, sum( sales), sum(quantity) is used to get total sales in day, week, month, time for item and resulting rows are summed. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

[PERFORM] Hash join on int takes 8..114 seconds

seconds. Any idea how to speed it up ? Is it possible to optimize it, will upgrading to 8.3.5 help or should I require to add more RAM, disk or CPU speed ? Real query contains column list instead of sum(1) used in test below. Andrus. explain analyze SELECT sum(1) FROM dok JOIN rid USING

Re: [PERFORM] Increasing select max(datecol) from bilkaib wheredatecol<=date'2008-11-01' and (cr='00' or db='00') speed

index: create index test on bilkaib (kuupaev) where cr='00' or db='00'; I have always cr='00' or db='00' clause. Separate values are never tested. I changed by queries back to old values and created this single index. This seems to be even better th

Re: [PERFORM] Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed

h. I added you indexes to db and re-write query. Now it runs fast. Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed

use other values in the query too. I'm sorry I do'nt understand this. What does the (cr, bilkaib) syntax mean? Should I create two functions indexes and re-write query as Vladimir suggests or is there better appoach ? Andrus. -- Sent via pgsql-performance mailing list (pgsql-p

[PERFORM] Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed

-write this query in any way or split to multiple statements if this makes it faster. Andrus. explain analyze select max(kuupaev) from bilkaib where kuupaev<=date'2008-11-01' and (cr='00' or db='00') "Result (cost=339.75..339.76 rows=1 width=0) (actual ti

[PERFORM] Using index for IS NULL query

94 rows=1 width=10)" "Filter: (dokumnr IS NULL)" Table makse contains 120 rows and about 800 rows with dokumnr is null so using index is much faster that seq scan. How to fix ? Andrus. "PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC)

Re: [PERFORM] Simple indexed IN query takes 40 seconds

) ... , will this fix the issue in 8.1.4 ? Andrus. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Simple indexed IN query takes 40 seconds

I found that simple IN query on indexed tables takes too much time. dok and rid have both indexes on int dokumnr columnr and dokumnr is not null. PostgreSql can use index on dok or event on rid so it can executed fast. How to make this query to run fast ? Andrus. note: list contain a lot

Re: [PERFORM] why group expressions cause query to run forever

null, casted to varchar and simplyfied the statment. However, this select statement runs forever. Any idea how to speed it up ? Andrus. SELECT bilkaib.DB, CASE WHEN dbkonto.objekt1='+' THEN bilkaib.DBOBJEKT ELSE null END::VARCHAR(10) AS dbobjekt, CASE WHEN dbkonto.objekt2='+

Re: [PERFORM] why group expressions cause query to run forever

quot; "lc_numeric";"et_EE.utf-8" "lc_time";"et_EE.utf-8" How to speed up this query ? Is it possible to force the binary comparison for grouping ? Should I concatenate all the char columns into single column ? Andrus. ---(en

[PERFORM] why group expressions cause query to run forever

t;-> Hash Join (cost=20.86..11676.02 rows=144696 width=25) (actual time=2.165..2076.951 rows=167349 loops=1)" " Hash Cond: ("outer".db = "inner".kontonr)" " -> Seq Scan on bilkaib (cost=0.00..9369.99 rows=167643 width=39) (actual time=0.012..725.813 rows=167349 loops=1)" "Filter: ((kuupaev >= '2006-01-01'::date) AND (kuupaev <= '2006-12-31'::date))" " -> Hash (cost=20.29..20.29 rows=227 width=14) (actual time=2.112..2.112 rows=227 loops=1)" "-> Seq Scan on konto dbkonto (cost=0.00..20.29 rows=227 width=14) (actual time=0.011..1.126 rows=227 loops=1)" " Filter: (iseloom = 'A'::bpchar)" "-> Hash (cost=20.29..20.29 rows=227 width=14) (actual time=2.149..2.149 rows=227 loops=1)" " -> Seq Scan on konto crkonto (cost=0.00..20.29 rows=227 width=14) (actual time=0.022..1.152 rows=227 loops=1)" "Filter: (iseloom = 'A'::bpchar)" "Total runtime: 4519.063 ms" Postgres 8.1 on Gentoo Linux. Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Why date index is not used

h row. I have 500 sales per day. So it can fetch 600 rows using index on kuupaev column. After that it can sort those 600 rows fast. Currently it sorts blindly all 54000 rows in table. > You'd need a two-column index on both of the ORDER BY columns to avoid > sorting. Thank you

Re: [PERFORM] Why date index is not used

e amout of work_mem for each process address space. I think that if I increase work_mem then swap file will became bigger and perfomance will decrease even more. How to increase perfomance ? Andrus. ---(end of broadcast)--- TIP 4: Have yo

Re: [PERFORM] Why date index is not used

is sales date. So this can contain 365 distinct values per year and max 10 year database, total can be 3650 distinct values after 10 years. Andrus ---(end of broadcast)--- TIP 6: explain analyze is your friend

[PERFORM] Why date index is not used

8.502 rows=53028 loops=1)" "Total runtime: 37857.177 ms" CREATE TABLE makse( kuupaev date, kellaaeg char(6) NOT NULL DEFAULT ''::bpchar, guid char(36) NOT NULL, CONSTRAINT makse_pkey PRIMARY KEY (guid) ) CREATE INDEX makse_kuupaev_idx ON makse USING btree (ku

Re: [PERFORM] How to force Postgres to use index on ILIKE

>> SELECT toode, nimetus >> FROM toode >> WHERE toode ILIKE 'x10%' ESCAPE '!' >> ORDER BY UPPER(toode ),nimetus LIMIT 100 >> >> runs 1 minute in first time for small table size. >> >> Toode field type is CHAR(20) > > 1) why are you using CHAR and not VARCHAR or TEXT? CHAR will give you

[PERFORM] How to force Postgres to use index on ILIKE

I have UTF-8 Postgres 8.1 database on W2K3 Query SELECT toode, nimetus FROM toode WHERE toode ILIKE 'x10%' ESCAPE '!' ORDER BY UPPER(toode ),nimetus LIMIT 100 runs 1 minute in first time for small table size. Toode field type is CHAR(20) How to create index on toode field so that qu

Re: [PERFORM] Query runs 38 seconds for small database!

e grows from zero to more than 219 times larger then it was still not processed. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Query runs 38 seconds for small database!

If default threshold is so conservative values I expect there is some reason for it. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Query runs 38 seconds for small database!

> The default autovac thresholds are not very aggressive; this table was > probably not large enough to get selected for analysis. Tom, thank you. Excellent. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your fre

Re: [PERFORM] Query runs 38 seconds for small database!

imate that far off about a simple single-column > condition, it means your statistics are out-of-date. Than you. I addded ANALYZE command and now query works fast. I see autovacuum: processing database "mydb" messages in log file and I have stats_start_collector = on stats_ro

[PERFORM] Query runs 38 seconds for small database!

I have small database. However the following query takes 38 (!) seconds to run. How to speed it up (preferably not changing table structures but possibly creating indexes) ? Andrus. set search_path to public,firma1; explain analyze select bilkaib.summa from BILKAIB join KONTO CRKONTO ON

Re: [PERFORM] CPU usage goes to 100%, query seems to ran forever

It's worth > taking time up front to design a clean table schema, and taking time > to revise it when requirements change. date range test in other part of where clause dok.kuupaev BETWEEN is optimizable. AND dok.kuupaev||dok.kellaaeg adds time range test to date range. Th

Re: [PERFORM] CPU usage goes to 100%, query seems to ran forever

Filter: ((toode = '1EEKPANT'::bpchar) AND (length(btrim((toode)::text)) > 2) AND (toode IS NOT NULL))" " -> Index Scan using toode_pkey on toode (cost=0.00..5.96 rows=1 width=43) (never executed)" " Index Cond: ('1EEKPANT'::bpchar = toode)" " -> Index Scan using artliik_pkey on artliik (cost=0.00..4.92 rows=1 width=31) (never executed)" "Index Cond: (("outer".grupp = artliik.grupp) AND ("outer".liik = artliik.liik))" "Total runtime: 492065.840 ms" Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend

[PERFORM] CPU usage goes to 100%, query seems to ran forever

yyp)::text) <> 0) AND (((kuupaev):: (..)" " -> Seq Scan on rid (cost=0.00..1019.42 rows=249 width=51)" "Filter: ((toode = '1EEKPANT'::bpchar) AND (length(btrim((toode)::text)) > 2) AND (toode IS NOT NULL))" "-> Index Scan using toode_pkey on toode (cost=0.00..5.94 rows=1 width=43)" " Index Cond: ('1EEKPANT'::bpchar = toode)" " -> Index Scan using artliik_pkey on artliik (cost=0.00..5.65 rows=1 width=88)" "Index Cond: (("outer".grupp = artliik.grupp) AND ("outer".liik = artliik.liik))" Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Query runs too long for indexed tables

PDATE command. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Query runs too long for indexed tables

_statement = error autovacuum = on ... also 2 stats settings from aurtovacuur max_fsm_pages = 3 Andrus. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

[PERFORM] Query runs too long for indexed tables

))) SubPlan -> Materialize (cost=2581.07..2944.41 rows=14734 width=84) -> Seq Scan on toode (cost=0.00..2350.34 rows=14734 width=84) Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings