Re: [PERFORM] Joins and full index scans...mysql vs postgres?

[Andreas Pflug]

Christopher Kings-Lynne wrote:
The pgAdmin query tool is known to give an answer about 5x the real 
answer - don't believe it!


Everybody please forget immediately the factor 5. It's no factor at all, 
but the GUI update time that is *added*, which depends on rows*columns.




ryan groth wrote:


the pgadmin query tool saying that the query runs
"997+3522 ms".


Means 997ms until all data is at the client (libpq reports the rowset), 
the rest is GUI overhead.


Regards,
Andreas

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[PERFORM] Created Index is not used

[Kjeld Peters]

Select and update statements are quite slow on a large table with more 
than 600,000 rows. The table consists of 11 columns (nothing special). 
The column "id" (int8) is primary key and has a btree index on it.


The following select statement takes nearly 500ms:

SELECT * FROM table WHERE id = 60;

A prepending "EXPLAIN" to the statement reveals a seq scan:

EXPLAIN SELECT * FROM table WHERE id = 60;

"Seq Scan on table  (cost=0.00..15946.48 rows=2 width=74)"
"  Filter: (id = 60)"

I tried a full vacuum and a reindex, but had no effect. Why is 
PostgreSQL not using the created index?


Or is there any other way to improve performance on this query?

The PostgreSQL installation is an out of the box installation with no 
further optimization. The server is running SUSE Linux 9.1, kernel 
2.6.4-52-smp. (Quad Xeon 2.8GHz, 1GB RAM)


SELECT version();
"PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 
(SuSE Linux)"



Thanks for any hints,
Kjeld

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] LIKE query on indexes

[Ibrahim Tekin]

hi,i ran a query with ILIKE but it doesn't use the index.but i tried following method, and it worked. there is 3 extra lower() overhead but i don't think it will effect the performance.CREATE INDEX index_name ON mytable (lower(column) varchar_pattern_ops);
SELECT * FROM mytable WHERE lower(column) LIKE lower('beginswith%')if insert operations are high in database. you use only this index to search case sensitive.say you want this:SELECT * FROM mytable WHERE column LIKE 'beGinsWith%'
write this:SELECT * FROM mytable WHERE lower(column) LIKE lower('beGinsWith%') AND column LIKE 'beGinsWith%'than query planner will search on index, than scan the resulting bitmap heap.
On 2/22/06, Brendan Duddridge <[EMAIL PROTECTED]> wrote:
Hi,Can this technique work with case insensitive ILIKE?It didn't seem to use the index when I used ILIKE instead of LIKE.Thanks, 
Brendan Duddridge | CTO | 403-277-5591 x24 |  
[EMAIL PROTECTED]  
ClickSpace Interactive Inc.  Suite L100, 239 - 10th Ave. SE  Calgary, AB  T2G 0V9 
http://www.clickspace.com   On Feb 21, 2006, at 1:28 PM, Ibrahim Tekin wrote:
this trick did the job. thanks.On 2/21/06, Alvaro Herrera <
[EMAIL PROTECTED]> wrote: Scott Marlowe wrote:> On Tue, 2006-02-21 at 09:57, Ibrahim Tekin wrote:
> > hi, > > i have btree index on a text type field. i want see rows which starts> > with certain characters on that field. so i write a query like this:> >> > SELECT * FROM mytable WHERE myfield LIKE 'john%' 
> >> > since this condition is from start of the field, query planner should> > use index to find such elements but explain command shows me it will> > do a sequential scan.> > 
> > is this lack of a feature or i am wrong somewhere?>> This is an artifact of how PostgreSQL handles locales other than ASCII.>> If you want such a query to use an index, you need to back up your 
> database, and re-initdb with --locale=C as an argument or you can choose to create an index with the text_pattern_opsoperator class, which would be used in a LIKE constraint regardless oflocale. 
http://www.postgresql.org/docs/8.1/static/indexes-opclass.html
--Alvaro Herrera http://www.CommandPrompt.com/The PostgreSQL Company - Command Prompt, Inc.

Re: [PERFORM] Created Index is not used

[Ragnar]

On fim, 2006-02-23 at 13:35 +0100, Kjeld Peters wrote:
> Select and update statements are quite slow on a large table with more 
> than 600,000 rows. The table consists of 11 columns (nothing special). 
> The column "id" (int8) is primary key and has a btree index on it.
> 
> The following select statement takes nearly 500ms:
> 
> SELECT * FROM table WHERE id = 60;
> 
> A prepending "EXPLAIN" to the statement reveals a seq scan:
> 
> EXPLAIN SELECT * FROM table WHERE id = 60;
> 
> "Seq Scan on table  (cost=0.00..15946.48 rows=2 width=74)"
> "  Filter: (id = 60)"

> I tried a full vacuum and a reindex, but had no effect. Why is 
> PostgreSQL not using the created index?

try one of:

SELECT * FROM table WHERE id = '60';
SELECT * FROM table WHERE id = 60::int8;
PostgreSQL 8+

gnari




---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Created Index is not used

[Markus Schaber]

Hi, Kjeld,

Kjeld Peters wrote:
> Select and update statements are quite slow on a large table with more
> than 600,000 rows. The table consists of 11 columns (nothing special).
> The column "id" (int8) is primary key and has a btree index on it.
> 
> The following select statement takes nearly 500ms:
> 
> SELECT * FROM table WHERE id = 60;

Known issue which is fixed in 8.X servers, postgreSQL sees your 60
as int4 literal and does not grasp that the int8 index works for it.

SELECT * FROM table WHERE id = 60::int8;

should do it.

> SELECT version();
> "PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3
> (SuSE Linux)"

Btw, you should update to 7.4.12, there are importand bug fixes and it
is upgradable "in place", without dumping and reloading the database.

HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Created Index is not used

[Kjeld Peters]

Hi Markus,

first of all thanks for your quick reply!

Markus Schaber wrote:

Kjeld Peters wrote:

Select and update statements are quite slow on a large table with more
than 600,000 rows. The table consists of 11 columns (nothing special).
The column "id" (int8) is primary key and has a btree index on it.

The following select statement takes nearly 500ms:

SELECT * FROM table WHERE id = 60;



Known issue which is fixed in 8.X servers, postgreSQL sees your 60
as int4 literal and does not grasp that the int8 index works for it.

SELECT * FROM table WHERE id = 60::int8;

should do it.


After I appended "::int8" to the query, selecting the table takes only 
40-50ms. That's a great performance boost!



Btw, you should update to 7.4.12, there are importand bug fixes and it
is upgradable "in place", without dumping and reloading the database.


I guess I'll test an upgrade to version 8.1.

Thanks again for your and Ragnar's help!

Kjeld

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [PERFORM]

[Vivek Khera]

On Feb 22, 2006, at 10:44 PM, Chethana, Rao ((IE10)) wrote:That is what I wanted to know,  how do I tune it?If there were a simple formula for doing it, it would already have been written up as a program that runs once you install postgres.You have to monitor your usage, use your understanding of your application, and the Postgres manual to see what things to adjust.   It differs if you are CPU bound or I/O bound.And please keep this on list.

[PERFORM] how to interpret/improve bad row estimates

[Robert Treat]

postgresql 8.1, I have two tables, bot hoth vacuumed and analyzed. on
msg307 I have altered the entityid and msgid columns statistics values
to 400. 


dev20001=# explain analyze  SELECT ewm.entity_id, m.agentname, m.filecreatedate 
AS versioninfo
   FROM msg307 m join entity_watch_map ewm on (ewm.entity_id = m.entityid AND 
ewm.msgid = m.msgid AND ewm.msg_type = 307);

  QUERY PLAN
   
---
 Nested Loop  (cost=6.62..5227.40 rows=1 width=36) (actual time=0.583..962.346 
rows=75322 loops=1)
   ->  Bitmap Heap Scan on entity_watch_map ewm  (cost=6.62..730.47 rows=748 
width=8) (actual time=0.552..7.017 rows=1264 loops=1)
 Recheck Cond: (msg_type = 307)
 ->  Bitmap Index Scan on ewm_msg_type  (cost=0.00..6.62 rows=748 
width=0) (actual time=0.356..0.356 rows=1264 loops=1)
   Index Cond: (msg_type = 307)
   ->  Index Scan using msg307_entityid_msgid_idx on msg307 m  (cost=0.00..6.00 
rows=1 width=40) (actual time=0.011..0.295 rows=60 loops=1264)
 Index Cond: (("outer".entity_id = m.entityid) AND ("outer".msgid = 
m.msgid))
 Total runtime: 1223.469 ms
(8 rows)


I guess that the planner can not tell there is no correlation between
the distinctness of those two columns, and so makes a really bad
estimate on the indexscan, and pushes that estimate up into the nested
loop? (luckily in this case doing an index scan is generally a good
idea, so it works out, but it wouldn't always be a good idea) 

some pg_statistics information for those two columns
entityid:
starelid| 25580
staattnum   | 1
stanullfrac | 0
stawidth| 4
stadistinct | 1266
stakind1| 1
stakind2| 2
stakind3| 3
stakind4| 0
staop1  | 96
staop2  | 97
staop3  | 97
staop4  | 0
stanumbers1 | {0.00222976,0.00222976,0.00153048,0.00137216,0.00137216}
stanumbers2 | 
stanumbers3 | {0.100312}
stanumbers4 | 

msgid:
starelid| 25580
staattnum   | 2
stanullfrac | 0
stawidth| 4
stadistinct | 1272
stakind1| 1
stakind2| 2
stakind3| 3
stakind4| 0
staop1  | 96
staop2  | 97
staop3  | 97
staop4  | 0
stanumbers1 | {0.00164923,0.00163604,0.00163604,0.00163604,0.00137216}
stanumbers2 | 
stanumbers3 | {-0.0660856}
stanumbers4 | 


is my interpretation of why i am seeing such bad estimates correct? I
don't really think it is, because looking at a similar scenario on a 7.3
machine:

--
 Merge Join  (cost=1531.39..5350.90 rows=1 width=48) (actual 
time=118.44..899.37 rows=58260 loops=1)
   Merge Cond: (("outer".entityid = "inner".entity_id) AND ("outer".msgid = 
"inner".msgid))
   ->  Index Scan using msg307_entityid_msgid_idx on msg307 m  
(cost=0.00..3669.42 rows=58619 width=40) (actual time=0.31..390.01 rows=58619 
loops=1)
   ->  Sort  (cost=1531.39..1533.16 rows=709 width=8) (actual 
time=118.09..157.45 rows=58218 loops=1)
 Sort Key: ewm.entity_id, ewm.msgid
 ->  Seq Scan on entity_watch_map ewm  (cost=0.00..1497.80 rows=709 
width=8) (actual time=0.14..114.74 rows=1157 loops=1)
   Filter: (msg_type = 307)
 Total runtime: 951.23 msec
(8 rows)


It still has the bad estimate at the nested loop stage, but it does seem
to have a better understanding of the # of rows it will return in the
index scan on msg307. This leads me to wonder if there something I could
do to improve the estimates on the 8.1 machine? 


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(end of broadcast)---
TIP 6: explain analyze is your friend

[PERFORM] Looking for a tool to "*" pg tables as ERDs

[Ron Peacetree]

Where "*" == 
{print | save to PDF | save to  format | display on screen}

Anyone know of one?

TiA
Ron

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] Looking for a tool to "*" pg tables as ERDs

[Markus Schaber]

Hi, Ron,

Ron Peacetree wrote:
> Where "*" == 
> {print | save to PDF | save to  format | display on screen}
> 
> Anyone know of one?

psql with fancy output formatting comes to my mind, or "COPY table TO
file" SQL command.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Looking for a tool to "*" pg tables as ERDs

[Vivek Khera]

On Feb 23, 2006, at 11:38 AM, Ron Peacetree wrote:


Where "*" ==
{print | save to PDF | save to  format | display on screen}

Anyone know of one?


There's a perl module, GraphViz::DBI::General, which does a rather  
nifty job of taking a schema and making a graphviz "dot" file from  
it, which can then be processed into any of a bazillion formats.


It basically makes a box for each table, with fields, and an arrow to  
each FK referenced table.  All layed out nicely.


You may also want to investigate the SQLFairy < http:// 
sqlfairy.sourceforge.net/ > if not for anything besides their awesome  
logo. :-)




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [PERFORM] Slow query

[Tom Lane]

"Jeremy Haile" <[EMAIL PROTECTED]> writes:
> I am running a query that joins against several large tables (~5 million
> rows each).  The query takes an exteremely long time to run, and the
> explain output is a bit beyond my level of understanding.  It is an
> auto-generated query, so the aliases are fairly ugly.

Yah :-(

> select distinct city4_.region_id as region1_29_, city4_1_.name as
> name29_, city4_.state_id as state2_30_ 
> from registered_voters registered0_ 
>  inner join registered_voter_addresses addresses1_ on
>  registered0_.registered_voter_id=addresses1_.registered_voter_id 
>  inner join registered_voter_addresses_regions regions2_ on
>  
> addresses1_.address_id=regions2_.registered_voter_addresses_address_id 
>  inner join regions region3_ on
>  regions2_.regions_region_id=region3_.region_id 
>  inner join cities city4_ on
>  addresses1_.city_id=city4_.region_id 
>  inner join regions city4_1_ on
>  city4_.region_id=city4_1_.region_id 
> where region3_.region_id='093c44e8-f3b2-4c60-8be3-2b4d148f9f5a' 
> order by city4_1_.name

AFAICS the planner is doing about the best you can hope the machine to
do --- it's not making any serious estimation errors, and the plan is
pretty reasonable for the given query.  The problem is that you are
forming a very large join result (4918204 rows) and then doing a
DISTINCT that reduces this to only 1124 rows ... but the damage of
computing that huge join has already been done.  The machine is not
going to be able to think its way out of this one --- it's up to you
to think of a better formulation of the query.

Offhand I'd try something involving joining just city4_/city4_1_
(which should not need DISTINCT, I think) and then using WHERE
EXISTS(SELECT ... FROM the-other-tables) to filter out the cities
you don't want.  The reason this can be a win is that the EXISTS
formulation will stop running the sub-select as soon as it's produced a
single row for the current city, rather than generating thousands of
similar rows that will be thrown away by DISTINCT as you have here.

This assumes that the fraction of cities passing the query is
substantial, as it appears from the rowcounts in your EXPLAIN output.
If only a tiny fraction of them passed, then the time wasted in failing
EXISTS probes might eat up the savings.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] Good News re count(*) in 8.1

[Kevin Grittner]

>>> On Wed, Feb 22, 2006 at  9:52 pm, in message
<[EMAIL PROTECTED]>, Greg Stark <[EMAIL PROTECTED]> wrote:


> "Kevin Grittner" <[EMAIL PROTECTED]> writes:
> 
>> There have been several times that I have run a SELECT COUNT(*) on
an entire
>> table on all central machines. On identical hardware, with identical
data,
>> and equivalent query loads, the PostgreSQL databases have responded
with a
>> count in 50% to 70% of the time of the commercial product, in spite
of the
>> fact that the commercial product does a scan of a non- clustered
index while
>> PostgreSQL scans the data pages.
> 
> I take it these are fairly narrow rows? The big benefit of index-
only scans
> come in when you're scanning extremely wide tables, often counting
rows
> matching some indexed criteria.

I'm not sure what you would consider "fairly narrow rows" -- so see the
attached.  This is the VACUUM ANALYZE VERBOSE output for the largest
table, from last night's regular maintenance run.

-Kevin




CaseHist-vacuum-analyze.txt
Description: Binary data

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] 0ut of Memory Error during Vacuum Analyze and

[Tomeh, Husam]

 
Thank for looking into this Tom. Here's the output from PostgreSQL log:

***  Postgresql Log:

TopMemoryContext: 32768 total in 4 blocks; 7232 free (9 chunks); 25536
used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
used
TopTransactionContext: 8192 total in 1 blocks; 6816 free (0 chunks);
1376 used
MessageContext: 8192 total in 1 blocks; 7104 free (1 chunks); 1088 used
smgr relation table: 8192 total in 1 blocks; 2872 free (0 chunks); 5320
used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1077575324 total in 115158 blocks; 1860896 free
(115146 chunks); 1075714428 used
ExecutorState: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 3896 free (0 chunks); 4296 used
CacheMemoryContext: 516096 total in 6 blocks; 198480 free (2 chunks);
317616 used
mort_ht: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_depend_depender_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_depend_reference_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_class_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_cast_source_target_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 328 free (0 chunks); 696
used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
MdSmgr: 8192 total in 1 blocks; 7504 free (0 chunks); 688 used
LockTable (locallock hash): 8192 total in 1 blocks; 3912 free (0
chunks); 4280 used
Timezones: 47592 total in 2 blocks; 5968 free (0 chunks); 41624 used
ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used
[2006-02-23 08:46:26 PST|[local]|mtrac|postgres] ERROR:  out of memory
[2006-02-23 08:46:26 PST|[local]|mtrac|postgres] DETAIL:  Failed on
request of size 134217728.

-

*** Stack trace:

I'm not having luck generating a stack trace so far. Following the gdb
instructions, the create index statement never comes back with either
the I/O error or a success (created index). I'm still trying to figure
this out. Hopefully, the above from the server log may shed some light
on the problem.

Thanks again,


 
Husam Tomeh 

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 14, 

Re: [PERFORM]

[Jim C. Nasby]

On Thu, Feb 23, 2006 at 09:38:25AM -0500, Vivek Khera wrote:
> 
> On Feb 22, 2006, at 10:44 PM, Chethana, Rao ((IE10)) wrote:
> 
> >That is what I wanted to know,  how do I tune it?
> 
> If there were a simple formula for doing it, it would already have  
> been written up as a program that runs once you install postgres.
> 
> You have to monitor your usage, use your understanding of your  
> application, and the Postgres manual to see what things to adjust.
> It differs if you are CPU bound or I/O bound.
> 
> And please keep this on list.
 
FWIW, had you included a bit more of the original post others might have
been able to provide advice... but now I have no idea what the original
question was (of course a blank subject doesn't help either... no idea
where that happened).

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Good News re count(*) in 8.1

[Jim C. Nasby]

On Thu, Feb 23, 2006 at 12:54:52PM -0600, Kevin Grittner wrote:
> >>> On Wed, Feb 22, 2006 at  9:52 pm, in message
> <[EMAIL PROTECTED]>, Greg Stark <[EMAIL PROTECTED]> wrote:
> 
> 
> > "Kevin Grittner" <[EMAIL PROTECTED]> writes:
> > 
> >> There have been several times that I have run a SELECT COUNT(*) on
> an entire
> >> table on all central machines. On identical hardware, with identical
> data,
> >> and equivalent query loads, the PostgreSQL databases have responded
> with a
> >> count in 50% to 70% of the time of the commercial product, in spite
> of the
> >> fact that the commercial product does a scan of a non- clustered
> index while
> >> PostgreSQL scans the data pages.
> > 
> > I take it these are fairly narrow rows? The big benefit of index-
> only scans
> > come in when you're scanning extremely wide tables, often counting
> rows
> > matching some indexed criteria.
> 
> I'm not sure what you would consider "fairly narrow rows" -- so see the
> attached.  This is the VACUUM ANALYZE VERBOSE output for the largest
> table, from last night's regular maintenance run.

Looks to be about 60 rows per page, somewhere around 140 bytes per row
(including overhead). Accounting for overhead and allowing for some
empty room, about 100 bytes of data per row, which isn't all that thin.
Not all that fat, either... The PK index is about 5 times smaller. IF
that ratio holds on the commercial product and they can't beat us with
an index scan :)
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] 0ut of Memory Error during Vacuum Analyze and

[Tomeh, Husam]

 
What's more interesting is this:

When I first connect to the database via "psql" and issue the "create
index" statement, of course,  I get the "out of memory" error. If I
don't quit my current session and re-ran the same DDL statement again,
the index gets created successfully!.. However, if after my first
unsuccessful run, I exit my session and re-connect again, and then run
the DDL, it will fail again and get the same error. I have done that for
many times and appears to have a consistent pattern of behavior. Not
sure if that'll help, but I thought it may be an interesting observation
to think about.


 
Husam Tomeh

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tomeh,
Husam
Sent: Thursday, February 23, 2006 11:57 AM
To: Tom Lane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] 0ut of Memory Error during Vacuum Analyze and

 
Thank for looking into this Tom. Here's the output from PostgreSQL log:

***  Postgresql Log:

TopMemoryContext: 32768 total in 4 blocks; 7232 free (9 chunks); 25536
used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
used
TopTransactionContext: 8192 total in 1 blocks; 6816 free (0 chunks);
1376 used
MessageContext: 8192 total in 1 blocks; 7104 free (1 chunks); 1088 used
smgr relation table: 8192 total in 1 blocks; 2872 free (0 chunks); 5320
used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1077575324 total in 115158 blocks; 1860896 free
(115146 chunks); 1075714428 used
ExecutorState: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 3896 free (0 chunks); 4296 used
CacheMemoryContext: 516096 total in 6 blocks; 198480 free (2 chunks);
317616 used
mort_ht: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_depend_depender_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_depend_reference_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_class_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_cast_source_target_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 328 free (0 chunks); 696
used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
MdSmgr: 8192 total in 1 block

Re: [PERFORM] 0ut of Memory Error during Vacuum Analyze and

[Tom Lane]

"Tomeh, Husam" <[EMAIL PROTECTED]> writes:
> When I first connect to the database via "psql" and issue the "create
> index" statement, of course,  I get the "out of memory" error. If I
> don't quit my current session and re-ran the same DDL statement again,
> the index gets created successfully!.. However, if after my first
> unsuccessful run, I exit my session and re-connect again, and then run
> the DDL, it will fail again and get the same error. I have done that for
> many times and appears to have a consistent pattern of behavior.

Now that you know how to reproduce it, please have another go at getting
that stack trace.  The palloc printout certainly looks like some kind of
memory-leak issue, but I can't tell more than that from it.

regards, tom lane

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

[PERFORM] Really really slow query. What's a better way?

[Brendan Duddridge]

Hi,We're executing a query that has the following plan and we're wondering given the size of the data set, what's a better way to write the query? It's been running since 2pm 2 days ago.explain DELETE FROM cds.cds_mspecxx WHERE ProdID not in (SELECT stage.ProdID FROM cds_stage.cds_Catalog stage where stage.countryCode = 'us') and countryCode = 'us';QUERY PLAN ---Index Scan using pk_mspecxx on cds_mspecxx (cost=53360.87..208989078645.48 rows=7377879 width=6)Index Cond: ((countrycode)::text = 'us'::text)Filter: (NOT (subplan))SubPlan-> Materialize (cost=53360.87..77607.54 rows=1629167 width=12)-> Seq Scan on cds_catalog stage (cost=0.00..43776.70 rows=1629167 width=12)Filter: ((countrycode)::text = 'us'::text)(7 rows)Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 |  [EMAIL PROTECTED]  ClickSpace Interactive Inc.  Suite L100, 239 - 10th Ave. SE  Calgary, AB  T2G 0V9 http://www.clickspace.com   

smime.p7s
Description: S/MIME cryptographic signature

Re: [PERFORM] Really really slow query. What's a better way?

[Christopher Kings-Lynne]

how about something like:

DELETE FROM cds.cds_mspecxx WHERE NOT EXISTS (SELECT 1 FROM 
cds_stage.cds_Catalog stage where stage.countryCode =  'us' and 
stage.ProdId=cds.cds_mspecxx.ProdId) and countryCode = 'us';


Run explain on it first to see how it will be planned.  Both tables 
should have an index over (countryCode, ProdId) I think.


Chris

Brendan Duddridge wrote:

Hi,

We're executing a query that has the following plan and we're wondering 
given the size of the data set, what's a better way to write the query? 
It's been running since 2pm 2 days ago.


explain DELETE FROM cds.cds_mspecxx WHERE ProdID not in (SELECT 
stage.ProdID FROM cds_stage.cds_Catalog stage where stage.countryCode = 
'us') and countryCode = 'us';
QUERY PLAN 
---
Index Scan using pk_mspecxx on cds_mspecxx 
(cost=53360.87..208989078645.48 rows=7377879 width=6)

Index Cond: ((countrycode)::text = 'us'::text)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=53360.87..77607.54 rows=1629167 width=12)
-> Seq Scan on cds_catalog stage (cost=0.00..43776.70 rows=1629167 width=12)
Filter: ((countrycode)::text = 'us'::text)
(7 rows)

Thanks,
*
*
*Brendan Duddridge* | CTO | 403-277-5591 x24 |  [EMAIL PROTECTED] 


*
*ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com 




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

Re: [PERFORM] Really really slow query. What's a better way?

[Brendan Duddridge]

Thanks Chris for the very quick response!

Just after posting this message, we tried explain on the same format  
as you just posted:


explain DELETE FROM cds.cds_mspecxx WHERE not exists (SELECT 'X' FROM  
cds_stage.cds_Catalog stage where stage.countryCode = 'us' and  
stage.prodid = cds.cds_mspecxx.prodid) and countryCode = 'us';

QUERY PLAN
 
--
Bitmap Heap Scan on cds_mspecxx (cost=299654.85..59555205.23  
rows=7377879 width=6)

Recheck Cond: ((countrycode)::text = 'us'::text)
Filter: (NOT (subplan))
-> Bitmap Index Scan on pk_mspecxx (cost=0.00..299654.85  
rows=14755759 width=0)

Index Cond: ((countrycode)::text = 'us'::text)
SubPlan
-> Index Scan using pk_catalog on cds_catalog stage (cost=0.00..7.97  
rows=2 width=0)
Index Cond: (((prodid)::text = ($0)::text) AND ((countrycode)::text =  
'us'::text))

(8 rows)

Seems way better. I'm not sure it can get any faster though. Not sure  
if having the indexes as (countryCode, ProdId) or (ProdId,  
countryCode) would make any kind of difference though. Would it?


Thanks!


Brendan Duddridge | CTO | 403-277-5591 x24 |  [EMAIL PROTECTED]

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com

On Feb 24, 2006, at 12:06 AM, Christopher Kings-Lynne wrote:


how about something like:

DELETE FROM cds.cds_mspecxx WHERE NOT EXISTS (SELECT 1 FROM  
cds_stage.cds_Catalog stage where stage.countryCode =  'us' and  
stage.ProdId=cds.cds_mspecxx.ProdId) and countryCode = 'us';


Run explain on it first to see how it will be planned.  Both tables  
should have an index over (countryCode, ProdId) I think.


Chris

Brendan Duddridge wrote:

Hi,
We're executing a query that has the following plan and we're  
wondering given the size of the data set, what's a better way to  
write the query? It's been running since 2pm 2 days ago.
explain DELETE FROM cds.cds_mspecxx WHERE ProdID not in (SELECT  
stage.ProdID FROM cds_stage.cds_Catalog stage where  
stage.countryCode = 'us') and countryCode = 'us';
QUERY PLAN  
- 
--
Index Scan using pk_mspecxx on cds_mspecxx  
(cost=53360.87..208989078645.48 rows=7377879 width=6)

Index Cond: ((countrycode)::text = 'us'::text)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=53360.87..77607.54 rows=1629167 width=12)
-> Seq Scan on cds_catalog stage (cost=0.00..43776.70 rows=1629167  
width=12)

Filter: ((countrycode)::text = 'us'::text)
(7 rows)
Thanks,
*
*
*Brendan Duddridge* | CTO | 403-277-5591 x24 |   
[EMAIL PROTECTED] 

*
*ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9
http://www.clickspace.com



---(end of  
broadcast)---

TIP 5: don't forget to increase your free space map settings





smime.p7s
Description: S/MIME cryptographic signature