[PERFORM] multi column query

Hi When I update a table that has 20 columns and the where clause includes 16 of the columns (this is a data warehousing type update on aggregate fields), The bitmap scan is not used by the optimizer. The table is indexed on 3 of the 20 fields. The update takes really long to finish (on a

Re: [PERFORM] multi column query

9:42 AM To: Sriram Dandapani Cc: Pgsql-Performance (E-mail) Subject: RE: [PERFORM] multi column query You need to run EXPLAIN ANALYZE. Also, what's random_page_cost set to? And the output of \d chkpfw_tr_dy_dimension. The cost for that index scan looks way too high. And please reply-all so

[PERFORM] slow cursor

Hi   I have a cursor that fetches 150K rows and updates or inserts a table with 150K rows.   It takes several minutes for the process to complete (about 15 minutes). The select by itself (without cursor) gets all rows in 15 seconds.   Is there a way to optimize the cursor to fetch all

[PERFORM] creating of temporary table takes very long

create temporary table c_chkpfw_hr_tr_updates as     select * from c_chkpfw_hr_tr a     where exists(select 1 from chkpfw_tr_hr_dimension b     WHERE a.firstoccurrence = b.firstoccurrence    

Re: [PERFORM] creating of temporary table takes very long

Explain analyze on the select statement that is the basis for temp table data takes forever. I turned off enable_seqscan but it did not have an effect   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sriram Dandapani Sent: Monday, April 17, 2006 11:37 AM To: Pgsql

Re: [PERFORM] creating of temporary table takes very long

Index Cond: (($0 = firstoccurrence) AND ($1 = sentryid_id) AND ($2 = node_id))" -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, April 17, 2006 12:29 PM To: Sriram Dandapani Cc: Pgsql-Performance (E-mail) Subject: Re: [PERFORM] creating of temporary table takes very

Re: [PERFORM] creating of temporary table takes very long

.144..12.144 rows=33026 loops=22001)" "Index Cond: (($0 = firstoccurrence) AND ($1 = sentryid_id) AND ($2 = node_id))" "Total runtime: 648097.800 ms" Regards Sriram -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, April 17, 2006

Re: [PERFORM] creating of temporary table takes very long

- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 18, 2006 9:10 AM To: Sriram Dandapani Cc: Pgsql-Performance (E-mail) Subject: Re: [PERFORM] creating of temporary table takes very long "Sriram Dandapani" <[EMAIL PROTECTED]> writes: > Got an explain analyze

[PERFORM] ip address data type

Hi   I have queries that use like operators and regex patterns to determine if an ip address is internal or external (this is against a table with say 100 million distinct ip addresses).   Does the inet data type offer comparison/search performance benefits over plain text for ip addres

[PERFORM] planner not using index for like operator

For the query     Select col1 from table1 Where col1 like ‘172.%’   The table has 133 million unique ip addresses. Col1 is indexed.   The optimizer is using a sequential scan   This is the explain analyze output   "Seq Scan on table1 (cost=0.00..2529284.80 rows=1 width=15) (actu

Re: [PERFORM] planner not using index for like operator

the inet data type? Thanks Sriram -Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 11:25 AM To: Sriram Dandapani Cc: Pgsql-Performance (E-mail) Subject: Re: [PERFORM] planner not using index for like operator On Tue, Apr 25, 2006 at 10:08

Re: [PERFORM] planner not using index for like operator

inet data type to see if there are differences. Sriram -Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 11:25 AM To: Sriram Dandapani Cc: Pgsql-Performance (E-mail) Subject: Re: [PERFORM] planner not using index for like operator On Tue, Apr 2

[PERFORM] select max(column) from parent table very slow

Parent table has a column say column1 which is indexed (parent table and all child tables are indexed on that column)   When a select max(column1) is done on parent table..takes a very long time to get back with the result The same query on a child table gives instantaneous response (the