Re: [PERFORM] count(*) using index scan in "query often, update rarely" environment
collision: it's possible to either block updating transaction until index scan ends or discard index scan imediately and finish query using MVCC compliant scan dead rows: this sounds like more serious counter-argument, I don't know much about dead records management and whether it would be possible/worth to make indexes matching live records when there's no transaction in progress on that table - Original Message - From: "Steinar H. Gunderson" <[EMAIL PROTECTED]> To: Sent: Friday, October 07, 2005 12:48 PM Subject: Re: [PERFORM] count(*) using index scan in "query often, update rarely" environment On Fri, Oct 07, 2005 at 11:24:05AM +0200, Cestmir Hybl wrote: Isn't it possible (and reasonable) for these environments to keep track of whether there is a transaction in progress with update to given table and if not, use an index scan (count(*) where) or cached value (count(*)) to perform this kind of query? Even if there is no running update, there might still be dead rows in the table. In any case, of course, a new update could always be occurring while your counting query was still running. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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] count(*) using index scan in "query often, update rarely" environment
Yes, I can possibly use triggers to maintanin counts of several fixed groups of records or total recordcount (but it's unpractical). No, I can't speed-up evaluation of generic "count(*) where ()" queries this way. My question was rather about general performance of count() queries in environment with infrequent updates. Cestmir - Original Message - From: hubert depesz lubaczewski To: Cestmir Hybl Cc: pgsql-performance@postgresql.org Sent: Friday, October 07, 2005 11:54 AM Subject: Re: [PERFORM] count(*) using index scan in "query often, update rarely" environment On 10/7/05, Cestmir Hybl <[EMAIL PROTECTED]> wrote: Isn't it possible (and reasonable) for these environments to keep track of whether there is a transaction in progress with update to given table and if not, use an index scan (count(*) where) or cached value (count(*)) to perform this kind of query? if i understand your problem correctly, then simple usage of triggers will do the job just fine.hubert
[PERFORM] count(*) using index scan in "query often, update rarely" environment
Hello all First of all, I do understand why pgsql with it's MVCC design has to examine tuples to evaluate "count(*)" and "count(*) where (...)" queries in environment with heavy concurrent updates. This kind of usage IMHO isn't the average one. There are many circumstances with rather "query often, update rarely" character. Isn't it possible (and reasonable) for these environments to keep track of whether there is a transaction in progress with update to given table and if not, use an index scan (count(*) where) or cached value (count(*)) to perform this kind of query? (sorry for disturbing if this was already discussed) Regards, Cestmir Hybl
Re: [PERFORM] MySQL vs PG TPC-H benchmarks
> Looks like he's using the default postgresql.conf settings in which case > I'm not suprised at pg looking so slow. The question also is, IMHO, why the hell, postgreSQL still comes out of the box with so stupid configuration defaults, totally underestimated for todays average hardware configuration (1+GHz, 0.5+GB RAM, fast FSB, fast HDD). It seems to me better strategy to force that 1% of users to "downgrade" cfg. than vice-versa. regards ch ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Ignoring index on (A is null), (A is not null) conditions
First of all, thanks for all your suggestions. They were of two classes: 1. use different data representation (special constant from column domain instead of NULL) This is possible, of course, but it makes data model less portable and requires changes in database abstraction layer of application. 2. use partial indexes This is suitable for single null-allowed column index. With increasing number of null-allowed columns inside index, the number of partial indexes required grows exponentially. All RDBMSs I ever used (Sybase, MSSQL, or even MySQL) were using index to filter by expressions containing is NULL conditions /(A is NULL), (A is not NULL), (A is NULL or A = const), (A is NULL or A > const)/ so it seems pretty strange to me that PostgreSQL does not. Is this sheduled feature at least? CH ---(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] Ignoring index on (A is null), (A is not null) conditions
Are you seeing this question as totally off-topic in this list, or there is really no one who knows something about indexing "is null" bits in postgres? Regards CH > Hi, > > suppose, for simplicity, there is a table with index like this: > > create table TABLE1 ( > A integer > ); > create index TABLE1_A on TABLE1 (A); > > My question is: why psql (7.3.3) does not use index when filtering by A IS > NULL, A IS NOT > NULL expressions? > > In fact, I need to filter by expression ((A is null) or (A > const)). > > Is there a way to filter by this expression using index? > > Functional index cannot be used (except strange solution with CASE-ing and > converting NULL values into some integer constant) > > > > -- -- > -- > Index Scan using table1_a on table1 (cost=0.00..437.14 rows=29164 width=4) >Index Cond: (a > 1000) > -- -- > -- > Seq Scan on table1 (cost=0.00..448.22 rows=1 width=4) >Filter: (a IS NULL) > > Seq Scan on table1 (cost=0.00..448.22 rows=30222 width=4) >Filter: (a IS NOT NULL) > > Seq Scan on table1 (cost=0.00..523.77 rows=29164 width=4) >Filter: ((a IS NULL) OR (a > 1000)) > > > > CH ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Ignoring index on (A is null), (A is not null) conditions
Hi, suppose, for simplicity, there is a table with index like this: create table TABLE1 ( A integer ); create index TABLE1_A on TABLE1 (A); My question is: why psql (7.3.3) does not use index when filtering by A IS NULL, A IS NOT NULL expressions? In fact, I need to filter by expression ((A is null) or (A > const)). Is there a way to filter by this expression using index? Functional index cannot be used (except strange solution with CASE-ing and converting NULL values into some integer constant) -- Index Scan using table1_a on table1 (cost=0.00..437.14 rows=29164 width=4) Index Cond: (a > 1000) -- Seq Scan on table1 (cost=0.00..448.22 rows=1 width=4) Filter: (a IS NULL) Seq Scan on table1 (cost=0.00..448.22 rows=30222 width=4) Filter: (a IS NOT NULL) Seq Scan on table1 (cost=0.00..523.77 rows=29164 width=4) Filter: ((a IS NULL) OR (a > 1000)) CH ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
