Re: [PERFORM] Weird index scan
Hi,
1) seems that the table is a view, I am wrong? If this is true, please
give a query to that table, and try to guess if there is already a bottleneck
there.
2) Add to the query an order by and try to find if it works better.
3) If you drop the index, and no other index exists, it will always use a
seqscan or other method to gather the rows. No other index is plausible to be
used there? (perhaps order by indexedcolumn may help).
A hint, drop that index, identify a usable index, and set enable_seqscan to
off; on your session (or as a global value on the conf file)
Best wishes,
Guido
>
> I'm getting weird results for one of my queries. The actual time of this
> index scan doesn't make any sense:
>
> -> Index Scan using dok_dok_fk_i on dokumendid a (cost=0.00..566.24
> rows=184 width=8) (actual time=0.170..420806.563 rows=1 loops=1)
>
> dok_dok_fk_i is index on dokumendid(dok_dok_id). Currently it contains
> mostly NULLs:
>
> pos1=# select dok_dok_id, count(1) from dokumendid group by dok_dok_id;
> dok_dok_id | count
> +---
> | 11423
>8034 |76
> (2 rows)
>
> If I drop the index, seq scan + sort is used instead and everything is
> fast again.
>
> The PostgreSQL version:
>
> pos1=# select version();
>version
>
> --
> PostgreSQL 7.4.5 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc
> (GCC) 3.3.4 (Debian 1:3.3.4-9)
> (1 row)
>
> The full EXPLAIN ANALYZE output:
>
> pos1=# explain analyze select * from v_inventuuri_vahed_kaubagrupiti;
>
> QUERY PLAN
>
>
> -
> Subquery Scan v_inventuuri_vahed_kaubagrupiti (cost=50896.04..50896.61
> rows=46 width=128) (actual time=437007.670..437007.817 rows=45 loops=1)
>-> Sort (cost=50896.04..50896.15 rows=46 width=42) (actual
> time=437007.664..437007.692 rows=45 loops=1)
> Sort Key: (COALESCE(sum(ir.summa_kmta), 0::numeric))::raha
> -> HashAggregate (cost=50893.85..50894.77 rows=46 width=42)
> (actual time=437007.229..437007.488 rows=45 loops=1)
>-> Hash Join (cost=5533.44..50807.93 rows=5728
> width=42) (actual time=436226.533..436877.499 rows=16271 loops=1)
> Hash Cond: ("outer".kau_kau_id = "inner".kau_id)
> -> Merge Right Join (cost=4759.52..49858.92
> rows=15696 width=26) (actual time=436117.333..436600.653 rows=16271
> loops=1)
>Merge Cond: (("outer".dok_dok_id =
> "inner".dok_id) AND ("outer".kau_kau_id = "inner".kau_kau_id))
>-> Index Scan using dor_dok_kau_i on
> dokumentide_read ar (cost=0.00..42789.44 rows=480962 width=19) (actual
> time=0.023..7873.117 rows=205879 loops=1)
>-> Sort (cost=4759.52..4798.76 rows=15696
> width=19) (actual time=428381.719..428392.204 rows=16271 loops=1)
> Sort Key: a.dok_id, ir.kau_kau_id
> -> Merge Left Join
> (cost=0.00..3665.65 rows=15696 width=19) (actual time=0.245..428279.595
> rows=16258 loops=1)
>Merge Cond: ("outer".dok_id =
> "inner".dok_dok_id)
>-> Nested Loop
> (cost=0.00..3620.23 rows=15696 width=19) (actual time=0.063..7243.529
> rows=16258 loops=1)
> -> Index Scan using dok_pk
> on dokumendid i (cost=0.00..3.73 rows=1 width=4) (actual
> time=0.030..0.035 rows=1 loops=1)
>Index Cond: (dok_id =
> 8034)
>Filter: (tyyp =
> 'IN'::bpchar)
> -> Index Scan using
> dor_dok_fk_i on dokumentide_read ir (cost=0.00..3459.55 rows=15696
> width=19) (actual time=0.023..7150.257 rows=16258 loops=1)
>Index Cond: (8034 =
> dok_dok_id)
>-> Index Scan using dok_dok_fk_i
> on dokumendid a (cost=0.00..566.24 rows=184 width=8) (actual
> time=0.170..420806.563 rows=1 loops=1)
> Filter: (tyyp =
> 'IA'::bpchar)
> -> Hash (cost=757.71..757.71 rows=6487 width=24)
> (actual time=109.178..109.178 rows=0 loops=1)
>-> Hash Join (cost=15.56..757.71 rows=6487
> width=24) (actual time=1.787..85.554 rows=17752 loops=1)
> Hash Cond: ("outer".kag_kag_id =
> "inner".a_kag_id)
> -> Seq Scan on kaubad k
> (cost=0.00..588.52 rows=17752 width=8) (a
Re: [PERFORM] Problem with 7.4.5 and webmin 1.8 in grant function
Sorry, just a fool tip, cause I haven't seen that you already done the pg_ctl
stop && pg_ctl start ...
(I mean, did you reload your conf settings?)
Regards,
Guido
> > > I used you perl script and found the error =>
> > > [EMAIL PROTECTED] tmp]# perl relacl.pl
> > > DBI connect('dbname=template1;port=5432','postgres',...) failed: FATAL:
> > IDENT
> > > authentication failed for user "postgres" at relacl.pl line 21
> > > Error in connect to DBI:Pg:dbname=template1;port=5432:
> > >
> > >
> > Excellent - we know what is going on now!
> >
> >
> > > And my pg_hba.conf is
> > >
> > > # IPv4-style local connections:
> > > hostall all 127.0.0.1 255.255.255.255 trust
> > > hostall all 192.168.0.0 255.255.0.0 trust
> > >
> > > trusted for every user.
> >
> > Ok, what I think has happened is that there is another Pg installation
> > (or another initdb'ed cluster) on this machine that you are accidentally
> > talking to. Try
> >
> > $ rpm -qa|grep -i postgres
> >
> > which will spot another software installation, you may just have to
> > search for files called pg_hba.conf to find another initdb'ed cluster
> >
> > This other installation should have a pg_hba.conf that looks something
> > like :
> >
> > local all allident
> > hostall all 127.0.0.1 255.255.255.255 ident
> >
> > So a bit of detective work is in order :-)
> >
> > Mark
> After being a detector I found that
> [EMAIL PROTECTED] ~]# rpm -qa|grep -i postgres
> postgresql-7.4.5-3.1.tlc
> postgresql-python-7.4.5-3.1.tlc
> postgresql-jdbc-7.4.5-3.1.tlc
> postgresql-tcl-7.4.5-3.1.tlc
> postgresql-server-7.4.5-3.1.tlc
> postgresql-libs-7.4.5-3.1.tlc
> postgresql-docs-7.4.5-3.1.tlc
> postgresql-odbc-7.3-8.1.tlc
> postgresql-pl-7.4.5-3.1.tlc
> postgresql-test-7.4.5-3.1.tlc
> postgresql-contrib-7.4.5-3.1.tlc
> [EMAIL PROTECTED] ~]#
>
> no other pg installation except the pgsql for windows in samba folder which I
> think it isn't matter ,is it?
> No other pg being run.
> [EMAIL PROTECTED] ~]# ps ax|grep postmaster
> 2228 ?S 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
> 3308 pts/0S+ 0:00 grep postmaster
> [EMAIL PROTECTED] ~]#
>
> Is it possible that it is related to pg_ident.conf ?
>
> Any comment please.
> Amrit,Thailand
>
>
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] [ADMIN] TOAST tables, cannot truncate
Ok, problem solved. A previous ALTER TABLE DROP COLUMN over this table was performed. By some reason, truncate didn't work then. Would like to know why...it does not seems to be a very frequent problem, due to the fact I only found one chat on the mailing lists talking about this issue, and was hard to find :( ! A dump of the table schema, passed to the psql command (cat table_dump.sql | psql xxx) worked fine. TRUNCATE is now available. Thanks. Guido > Sorry for crossposting, didn't know where to post. > > Any hint/help on this?! > > db_postgres1=# truncate ref_v2_drs_valid_product ; > ERROR: expected both swapped tables to have TOAST tables > > I need to truncate this table, this is the first time I see this error. > > Regards, > Guido > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] TOAST tables, cannot truncate
Sorry for crossposting, didn't know where to post. Any hint/help on this?! db_postgres1=# truncate ref_v2_drs_valid_product ; ERROR: expected both swapped tables to have TOAST tables I need to truncate this table, this is the first time I see this error. Regards, Guido ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] [ADMIN] slower every day
Thanks for the reply, Been reading hackers of Aug 2004 and found the threads. It's a common habit to create two lines on the configuration files, in order to maintain the copy of the default conf file. I guess this should be the worst scenery for a freshly incoming DBA trying to put things in order. A temporary patch, will be updating documentation, encouraging administrators to use the SHOW ALL; command in the psql env, to confirm that changes where made. In my case, a 1.2 gig file was written, performance was on the floor. And my previous situation, a reindex force task last saturday, confused me. This is not a trivial problem, but in conjunction with other small problems could become a big one. Good habits when touching conf files & using the SHOW ALL to confirm that changes where made will help until this is patched. Thanks for Postgres, Regards, Guido. > This issue was resently discussed on hackers. It is a known issue, not very > convinient for the user. Nevertheless it is not fixed in 8.0, but will > perhaps be addressed in the next major release. > (Remembering, it was a non-trivial thing to change.) > > Best Regards, > Michael Paesold > > G u i d o B a r o s i o wrote: > > > The solution appeared as something I didn't know > > > > On the .conf file > > > > Previous situation: > > > > #log_something=false > > log_something=true > > > > Worst situation > > #log_something=false > > #log_something=true > > > > Nice situation > > log_something=false > > #log_something=true > > > > > > Ok, the problem was that I assumed that commenting a value on > > the conf file will set it up to a default (false?). I was wrong. > > My server was writting tons of log's. > > > > Is this the normal behavior for pg_ctl reload? It seems that looks for new > values, remembering the last state on the ones that actually are commented. > Although it's my fault to have 2 (tow) lines for the same issue, and that I > should realize that this is MY MISTAKE, the log defaults on a reload, if > commented, tend to be the last value entered? > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [ADMIN] slower every day
Again me, To make it easier. Situation A: log_something = true Situation B: # log_something = Situation C: log_something = false After the pg_ctl reload: Situation B = Situation A Situation C <> (Situation A || Situation B) Is this the expected behavior? Conclusion: If you comment a line on the conf file, and reload it, will remain in the last state. (either wast true or false, while I expected a default) Regards > The solution appeared as something I didn't know > > On the .conf file > > Previous situation: > > #log_something=false > log_something=true > > Worst situation > #log_something=false > #log_something=true > > Nice situation > log_something=false > #log_something=true > > > Ok, the problem was that I assumed that commenting a value on > the conf file will set it up to a default (false?). I was wrong. > My server was writting tons of log's. > > Is this the normal behavior for pg_ctl reload? It seems that looks for new values, > remembering the last state on the ones that actually are commented. Although it's my > fault to have 2 (tow) lines for the same issue, and that I should realize that this > is MY MISTAKE, the log defaults on a reload, if commented, tend to be the last value > entered? > > Regards, > Guido > > > > Am Mittwoch, 1. September 2004 12:06 schrieb G u i d o B a r o s i o: > > > The problem is the time that the postgres takes to perform/return a > > > query. For example, trying the \d command takes between 4 or 5 > > > seconds. This table is very big, but I am not asking for the rows, only > > > asking the table schema, so...why is this so slow?!?!? My last > > > administrative action into this table was a reindex to all the indexes via > > > the BKI in standalone mode. I thought I suceed, but this was las saturday. > > > > Do you regularly vacuum and analyze the database? > > > > -- > > Peter Eisentraut > > http://developer.postgresql.org/~petere/ > > > > ---(end of broadcast)--- > > TIP 8: explain analyze is your friend > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [ADMIN] slower every day
The solution appeared as something I didn't know On the .conf file Previous situation: #log_something=false log_something=true Worst situation #log_something=false #log_something=true Nice situation log_something=false #log_something=true Ok, the problem was that I assumed that commenting a value on the conf file will set it up to a default (false?). I was wrong. My server was writting tons of log's. Is this the normal behavior for pg_ctl reload? It seems that looks for new values, remembering the last state on the ones that actually are commented. Although it's my fault to have 2 (tow) lines for the same issue, and that I should realize that this is MY MISTAKE, the log defaults on a reload, if commented, tend to be the last value entered? Regards, Guido > Am Mittwoch, 1. September 2004 12:06 schrieb G u i d o B a r o s i o: > > The problem is the time that the postgres takes to perform/return a > > query. For example, trying the \d command takes between 4 or 5 > > seconds. This table is very big, but I am not asking for the rows, only > > asking the table schema, so...why is this so slow?!?!? My last > > administrative action into this table was a reindex to all the indexes via > > the BKI in standalone mode. I thought I suceed, but this was las saturday. > > Do you regularly vacuum and analyze the database? > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] slower every day
Dear all, I am currently experiencing troubles with the performance of my critical's database. The problem is the time that the postgres takes to perform/return a query. For example, trying the \d command takes between 4 or 5 seconds. This table is very big, but I am not asking for the rows, only asking the table schema, so...why is this so slow?!?!? My last administrative action into this table was a reindex to all the indexes via the BKI in standalone mode. I thought I suceed, but this was las saturday. Today I am in the same situation again. The only change that I've done was a highest level of debug in the conf file (loggin lot of stuff). I understand that this could lack on performance, but when I've changed the .conf file to the usual .conf file (with less debug), and pg_ctl reload(ed) it, it goes on debuging as in the first state, in the higher level. Is this a known issue? My conclusion is that I can aquire high levels of debug while the server is running, editing the .conf file, and pg_reload(ing) it, but I can go back then, unless I pg_restart the server. Is this ok? Some info --- PostgreSQL 7.4.2 [EMAIL PROTECTED] data]$ pg_config --configure '--enable-thread-safety' '--with-perl' Intel(R) Xeon(TM) MP CPU 2.80GHz Linux 2.4.24-ck1 #5 SMP Fri Mar 12 23:41:51 GMT 2004 i686 unknown RAM 4 Gb. --- Thanks, Guido. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] insert
As I see it's 100 inserts every 5 minutes, not only 100 inserts. Sure it's extreme for only 100 inserts. Cheers, Guido > "G u i d o B a r o s i o" <[EMAIL PROTECTED]> wrote: > > [speeding up 100 inserts every 5 minutes] > > > Tips! > > *Delete indexes and recreate them after the insert. > > sounds a bit extreme, for only 100 inserts > > gnari > > > > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] insert
Tips! *Delete indexes and recreate them after the insert. *Disable auto-commit *Perform a copy will be faster, sure. Best wishes, Guido > Hi, > > is there anything I can doo to speed up inserts? One of my tables gets > about 100 new rows every five minutes. And somehow the inserts tend to > take more and more time. > > Any suggestions welcome. > > TIA > > Ulrich > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Temporary tables
The box: Linux 2.4.24-ck1 8 Intel(R) Xeon(TM) MP CPU 2.80GHz 4 gb RAM. Postgresql 7.4.2 The problem: Short in disk space. (waiting new hard) The real problem: Developers usually write queries involving the creation of temporary tables. The BF question: Is a good idea to link this tmp tables to another partition? If so, how can I link this tmp tables to another partition? Suggestions? Thanks in advance! Guido ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] No index usage with
TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Greetz, Guido > Cannot you do a cast in your query? Does that help with using the indexes? > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of > [EMAIL PROTECTED] > Sent: maandag 2 augustus 2004 14:09 > To: [EMAIL PROTECTED] > Subject: [PERFORM] No index usage with "left join" > > > We have a "companies" and a "contacts" table with about 3000 records > each. > > We run the following SQL-Command which runs about 2 MINUTES !: > > SELECT count(*) FROM contacts LEFT JOIN companies ON contacts.sid = > companies.intfield01 > > contacts.sid (type text, b-tree index on it) > companies.intfield01 (type bigint, b-tree index on it) > > comfire=> explain analyze SELECT count(*) FROM prg_contacts LEFT JOIN > prg_addresses ON prg_contacts.sid=prg_addresses.intfield01; > NOTICE: QUERY PLAN: > > Aggregate (cost=495261.02..495261.02 rows=1 width=15) (actual > time=40939.38..40939.38 rows=1 loops=1) > -> Nested Loop (cost=0.00..495253.81 rows=2885 width=15) (actual > time=0.05..40930.14 rows=2866 loops=1) > -> Seq Scan on prg_contacts (cost=0.00..80.66 rows=2866 > width=7) (actual time=0.01..18.10 rows=2866 loops=1) > -> Seq Scan on prg_addresses (cost=0.00..131.51 rows=2751 > width=8) (actual time=0.03..6.25 rows=2751 loops=2866) > Total runtime: 40939.52 msec > > EXPLAIN > > Note: > - We need the left join because we need all contacts even if they are > not assigned to a company > - We are not able to change the datatypes of the joined fields > because we use a standard software (btw who cares: SuSE Open Exchange > Server) > - When we use a normal join (without LEFT or a where clause) the SQL > runs immediately using the indexes > > How can I force the usage of the indexes when using "left join". Or > any other SQL construct that does the same !? Can anybody please give > us a hint !? > > Thanks in forward. > > Greetings > Achim > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
