Re: [GENERAL] Indexes being ignored after upgrade to 9.5
Hi Peter, Many thanks for your response. I tried to cancel the thread, it was unfortunately stupidity that was the issue. We'd been forced to manually analyze our tables due to time constraints, and one of the table partitions read in the query was missed. It was reporting a bitmap index scan on the parent so we thought all was ok, and was then causing other tables to sequential scan. A further misunderstanding was that an explain analyze would initiate stats gathering on all queried tables, however this is not the case. Thanks again for your response, we'll check the behaviour you report. Best regards Nick > On 27 Jul 2017, at 00:40, Peter Geoghegan wrote: > >> On Wed, Jul 26, 2017 at 2:05 PM, Peter Geoghegan wrote: >>> On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan wrote: >>> We've added duplicate indexes and analyzing, however the new indexes are >>> still ignored unless we force using enable_seqscan=no or reduce >>> random_page_cost to 2. The query response times using the new indexes are >>> still as slow when we do this. Checking pg_stat_user_indexes the number of >>> tuples returned per idx_scan is far greater after the upgrade than before. >>> All indexes show valid in pg_indexes. > > I assume that you mean that pg_stat_user_indexes.idx_tup_read is a lot > higher than before, in proportion to pg_stat_user_indexes.idx_scan. > What about the ratio between pg_stat_user_indexes.idx_tup_read and > pg_stat_user_indexes.idx_tup_fetch? How much has that changed by? > > -- > Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes being ignored after upgrade to 9.5
On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan wrote: > Hi, > > We have recently promoted our Prod DB slave (2TB) to migrate to new > hardware, and upgraded from v9.2.9.21 to 9.5.1.6 using pg_upgrade. > > > The upgrade went without incident and we have been running for a week, but > the optimizer is ignoring indexes on 2 of our largest partitioned tables > causing very slow response times. > > > The indexes are Btree indexes on BIGINT columns, which the optimizer used > to return queries with ms response times on 9.2. Post-upgrade the queries > sequential scan and do not use indexes unless we force them. > Can you show the explain (analyze) plans for both forcing and non-forcing? And with both 9.5 and the old 9.2, if that is still available. > > We've added duplicate indexes and analyzing, however the new indexes are > still ignored unless we force using enable_seqscan=no or reduce > random_page_cost to 2. The query response times using the new indexes are > still as slow when we do this. > Still as slow as what? As slow as when you use the seq scan, or as slow as when you used index scans back under 9.2, or as slow as the the non-duplicate indexes were? Cheers, Jeff
Re: [GENERAL] Indexes being ignored after upgrade to 9.5
On Wed, Jul 26, 2017 at 2:05 PM, Peter Geoghegan wrote: > On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan wrote: >> We've added duplicate indexes and analyzing, however the new indexes are >> still ignored unless we force using enable_seqscan=no or reduce >> random_page_cost to 2. The query response times using the new indexes are >> still as slow when we do this. Checking pg_stat_user_indexes the number of >> tuples returned per idx_scan is far greater after the upgrade than before. >> All indexes show valid in pg_indexes. I assume that you mean that pg_stat_user_indexes.idx_tup_read is a lot higher than before, in proportion to pg_stat_user_indexes.idx_scan. What about the ratio between pg_stat_user_indexes.idx_tup_read and pg_stat_user_indexes.idx_tup_fetch? How much has that changed by? -- Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes being ignored after upgrade to 9.5
On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan wrote: > We've added duplicate indexes and analyzing, however the new indexes are > still ignored unless we force using enable_seqscan=no or reduce > random_page_cost to 2. The query response times using the new indexes are > still as slow when we do this. Checking pg_stat_user_indexes the number of > tuples returned per idx_scan is far greater after the upgrade than before. > All indexes show valid in pg_indexes. > > > We have tried increasing effective_cache_size but no effect (the queries > appear to go slower). The DB is 24x7 so we cannot reindex the tables/ > partitions. > > > Can anyone suggest why this would be happening? Are the indexes bloated? Are they larger than before, as indicated by psql's \di+ or similar? Did you notice that this happened immediately, or did it take a while? Are these unique indexes or not? Do you have a workload with many UPDATEs? I ask all these questions because I think it's possible that this is explained by a regression in 9.5's handling of index bloat, described here: http://postgr.es/m/CAH2-Wz=sfakvmv1x9jh19ej8am8tzn9f-yecips9hrrrqss...@mail.gmail.com -- Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Indexes being ignored after upgrade to 9.5
Hi, We have recently promoted our Prod DB slave (2TB) to migrate to new hardware, and upgraded from v9.2.9.21 to 9.5.1.6 using pg_upgrade. The upgrade went without incident and we have been running for a week, but the optimizer is ignoring indexes on 2 of our largest partitioned tables causing very slow response times. The indexes are Btree indexes on BIGINT columns, which the optimizer used to return queries with ms response times on 9.2. Post-upgrade the queries sequential scan and do not use indexes unless we force them. We've added duplicate indexes and analyzing, however the new indexes are still ignored unless we force using enable_seqscan=no or reduce random_page_cost to 2. The query response times using the new indexes are still as slow when we do this. Checking pg_stat_user_indexes the number of tuples returned per idx_scan is far greater after the upgrade than before. All indexes show valid in pg_indexes. We have tried increasing effective_cache_size but no effect (the queries appear to go slower). The DB is 24x7 so we cannot reindex the tables/ partitions. Can anyone suggest why this would be happening? Many thanks Nick
Re: [GENERAL] Indexes and MVCC
On Sun, Feb 19, 2017 at 8:52 AM, Rakesh Kumar wrote: > > https://www.youtube.com/watch?v=8mKpfutwD0U&t=1741s > > Somewhere around 13th minute, Chris Tavers mentions this: > > 1 - In the queuing table, the application deletes lot of rows (typical for > a queuing table). > 2 - Query trying to find out rows to be picked by the queue, accesses them > via indexes. > 3 - Vacuum took lot of time to clean up dead rows. > 4 - Indexes do not reach out to the latest visible tuple and has to > traverse lot of dead > rows before hitting the row required by the sql. This is because of > (3). > > My Question: > Is the (4) true for all updates. AFAIK, if an update occurs on even one > index col, the > index itself creates a new version in MVCC. > The index doesn't create a new version, but there is created a new pointer in the index to the new version in the table. But it doesn't remove the pointer to the old version at the time the new pointer is created. > Only HOT updates will end up in situation > described in (3). > No, quite the opposite. Anyone can clean up a HOT update, once the previous version is old enough that it can't be of interest to anyone anymore. Only vacuum can completely remove the dead tuple from a non-HOT update. However, btree indexes have a feature where if they find a tuple which is old enough that it can't be interesting to anyone, they remember that and when they get back to the index they clean up the index entry, so the next process doesn't need to follow it. But the key here is that the tuple has to be old enough that it is not interesting to *anyone*. If there is a long-lived transaction, even if it is not interested in this particular table, it will inhibit this mechanism from working (as well as inhibiting vacuum itself from cleaning them up) Cheers, Jeff
[GENERAL] Indexes and MVCC
https://www.youtube.com/watch?v=8mKpfutwD0U&t=1741s Somewhere around 13th minute, Chris Tavers mentions this: 1 - In the queuing table, the application deletes lot of rows (typical for a queuing table). 2 - Query trying to find out rows to be picked by the queue, accesses them via indexes. 3 - Vacuum took lot of time to clean up dead rows. 4 - Indexes do not reach out to the latest visible tuple and has to traverse lot of dead rows before hitting the row required by the sql. This is because of (3). My Question: Is the (4) true for all updates. AFAIK, if an update occurs on even one index col, the index itself creates a new version in MVCC. Only HOT updates will end up in situation described in (3).
Re: [GENERAL] Indexes and loops
Hi 2016-12-27 19:05 GMT+01:00 Арсен Арутюнян : > Hello. > > I have a few questions: > > 1) JobStatusTest1 function has only one request and JobStatusTest2 > function has as many as six requests. > > Why function JobStatusTest2 is faster? > > > JobStatusTest1 : 981.596 ms > > JobStatusTest2 : 849.133 ms > > > 2) Two functions JobStatusTest3 and JobStatusTest4 perform the same > actions. But one of the function performs the same steps in the cycle. > > Why is the speed of the functions so incredibly much different? > > > JobStatusTest3 : 1430.777 ms > > JobStatusTest4 : 2.386 ms > > > best > Arsen Arutyunyan > > = > > CREATE TABLE test_job(id serial,primary key(id)); > insert into test_job (id) values (1); > CREATE TABLE test_status(id serial,primary key(id)); > insert into test_status (id) values (1),(2),(4),(8),(16),(32); > CREATE TABLE test_task(id serial,job_id integer references test_job on > delete cascade,status_id integer references test_status on delete > cascade,primary key(id)); > CREATE INDEX CONCURRENTLY test_job_idx on test_job(id); > CREATE INDEX CONCURRENTLY test_status_idx on test_status(id); > CREATE INDEX CONCURRENTLY test_task_idx on test_task(id); > CREATE INDEX CONCURRENTLY test_task_idx_cnt1 on test_task(id,job_id) where > status_id=1; > CREATE INDEX CONCURRENTLY test_task_idx_cnt2 on test_task(id,job_id) where > status_id=2; > CREATE INDEX CONCURRENTLY test_task_idx_cnt3 on test_task(id,job_id) where > status_id=4; > CREATE INDEX CONCURRENTLY test_task_idx_cnt4 on test_task(id,job_id) where > status_id=8; > CREATE INDEX CONCURRENTLY test_task_idx_cnt5 on test_task(id,job_id) where > status_id=16; > CREATE INDEX CONCURRENTLY test_task_idx_cnt6 on test_task(id,job_id) where > status_id=32; > > > insert into test_task (id,job_id,status_id) values > (generate_series(1,10,1),1,1); > insert into test_task (id,job_id,status_id) values (generate_series(11, > 60,1),1,2); > insert into test_task (id,job_id,status_id) values (generate_series(61, > 100,1),1,4); > insert into test_task (id,job_id,status_id) values > (generate_series(101,170,1),1,8); > insert into test_task (id,job_id,status_id) values > (generate_series(171,250,1),1,16); > insert into test_task (id,job_id,status_id) values > (generate_series(251,300,1),1,32); > > > CREATE OR REPLACE FUNCTION JobStatusTest1(JobID integer) RETURNS void AS $$ > DECLARE > CurrentQuery RECORD; > BEGIN > FOR CurrentQuery IN select count(test_task.id) as counter, status_id from > test_job inner join test_task on (test_job.id=test_task.job_id) where > test_job.id=JobID group by status_id LOOP > raise notice 'Conter:% Status:%', CurrentQuery.counter, > CurrentQuery.status_id; > END LOOP; > END; > $$ LANGUAGE plpgsql; > > explain analyze select * from JobStatusTest1(1); > > CREATE OR REPLACE FUNCTION JobStatusTest2(JobID integer) RETURNS void AS $$ > DECLARE > CurrentQuery RECORD; > counter integer; > BEGIN > select count(test_task.id) into counter from test_job inner join > test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and > test_task.status_id=1; > raise notice 'Conter:% Status:%', counter, 1; > select count(test_task.id) into counter from test_job inner join > test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and > test_task.status_id=2; > raise notice 'Conter:% Status:%', counter, 2; > select count(test_task.id) into counter from test_job inner join > test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and > test_task.status_id=4; > raise notice 'Conter:% Status:%', counter, 4; > select count(test_task.id) into counter from test_job inner join > test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and > test_task.status_id=8; > raise notice 'Conter:% Status:%', counter, 8; > select count(test_task.id) into counter from test_job inner join > test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and > test_task.status_id=16; > raise notice 'Conter:% Status:%', counter, 16; > select count(test_task.id) into counter from test_job inner join > test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and > test_task.status_id=32; > raise notice 'Conter:% Status:%', counter, 32; > END; > $$ LANGUAGE plpgsql; > > explain analyze select * from JobStatusTest2(1); > > CREATE OR REPLACE FUNCTION JobStatusTest3(JobID integer) RETURNS void AS $$ > DECLARE > CurrentQuery RECORD; > taskid integer; > BEGIN > FOR CurrentQuery IN select id from test_status LOOP > SELECT test_task.id into taskid from test_job inner join test_task on ( > test_job.id=test_task.job_id) where test_job.id=JobID and > test_task.status_id=CurrentQuery.id ORDER BY test_task.id limit 1; > raise notice 'TaskID:% Status:%', taskid, CurrentQuery.id; > END LOOP; > END; > $$ LANGUAGE plpgsql; > > explain analyze select * from JobStatusTest3(1); > > CREATE OR REPLACE FUNCTION
[GENERAL] Indexes and loops
Hello. I have a few questions: 1) JobStatusTest1 function has only one request and JobStatusTest2 function has as many as six requests. Why function JobStatusTest2 is faster? JobStatusTest1 : 981.596 ms JobStatusTest2 : 849.133 ms 2) Two functions JobStatusTest3 and JobStatusTest4 perform the same actions. But one of the function performs the same steps in the cycle. Why is the speed of the functions so incredibly much different? JobStatusTest3 : 1430.777 ms JobStatusTest4 : 2.386 ms best Arsen Arutyunyan = CREATE TABLE test_job(id serial,primary key(id)); insert into test_job (id) values (1); CREATE TABLE test_status(id serial,primary key(id)); insert into test_status (id) values (1),(2),(4),(8),(16),(32); CREATE TABLE test_task(id serial,job_id integer references test_job on delete cascade,status_id integer references test_status on delete cascade,primary key(id)); CREATE INDEX CONCURRENTLY test_job_idx on test_job(id); CREATE INDEX CONCURRENTLY test_status_idx on test_status(id); CREATE INDEX CONCURRENTLY test_task_idx on test_task(id); CREATE INDEX CONCURRENTLY test_task_idx_cnt1 on test_task(id,job_id) where status_id=1; CREATE INDEX CONCURRENTLY test_task_idx_cnt2 on test_task(id,job_id) where status_id=2; CREATE INDEX CONCURRENTLY test_task_idx_cnt3 on test_task(id,job_id) where status_id=4; CREATE INDEX CONCURRENTLY test_task_idx_cnt4 on test_task(id,job_id) where status_id=8; CREATE INDEX CONCURRENTLY test_task_idx_cnt5 on test_task(id,job_id) where status_id=16; CREATE INDEX CONCURRENTLY test_task_idx_cnt6 on test_task(id,job_id) where status_id=32; insert into test_task (id,job_id,status_id) values (generate_series(1,10,1),1,1); insert into test_task (id,job_id,status_id) values (generate_series(11,60,1),1,2); insert into test_task (id,job_id,status_id) values (generate_series(61,100,1),1,4); insert into test_task (id,job_id,status_id) values (generate_series(101,170,1),1,8); insert into test_task (id,job_id,status_id) values (generate_series(171,250,1),1,16); insert into test_task (id,job_id,status_id) values (generate_series(251,300,1),1,32); CREATE OR REPLACE FUNCTION JobStatusTest1(JobID integer) RETURNS void AS $$ DECLARE CurrentQuery RECORD; BEGIN FOR CurrentQuery IN select count(test_task.id) as counter, status_id from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID group by status_id LOOP raise notice 'Conter:% Status:%', CurrentQuery.counter, CurrentQuery.status_id; END LOOP; END; $$ LANGUAGE plpgsql; explain analyze select * from JobStatusTest1(1); CREATE OR REPLACE FUNCTION JobStatusTest2(JobID integer) RETURNS void AS $$ DECLARE CurrentQuery RECORD; counter integer; BEGIN select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=1; raise notice 'Conter:% Status:%', counter, 1; select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=2; raise notice 'Conter:% Status:%', counter, 2; select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=4; raise notice 'Conter:% Status:%', counter, 4; select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=8; raise notice 'Conter:% Status:%', counter, 8; select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=16; raise notice 'Conter:% Status:%', counter, 16; select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=32; raise notice 'Conter:% Status:%', counter, 32; END; $$ LANGUAGE plpgsql; explain analyze select * from JobStatusTest2(1); CREATE OR REPLACE FUNCTION JobStatusTest3(JobID integer) RETURNS void AS $$ DECLARE CurrentQuery RECORD; taskid integer; BEGIN FOR CurrentQuery IN select id from test_status LOOP SELECT test_task.id into taskid from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=CurrentQuery.id ORDER BY test_task.id limit 1; raise notice 'TaskID:% Status:%', taskid, CurrentQuery.id; END LOOP; END; $$ LANGUAGE plpgsql; explain analyze select * from JobStatusTest3(1); CREATE OR REPLACE FUNCTION JobStatusTest4(JobID integer) RETURNS void AS $$ DECLARE CurrentQuery RECORD; taskid integer; BEGIN SELECT test_task.id into taskid from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=1 ORDER BY test_task.id limit 1
[GENERAL] Indexes and loops or some kind of pg error?
Hello. I have a few questions: 1) JobStatusTest1 function has only one request and JobStatusTest2 function has as many as six requests. Why function JobStatusTest2 is faster? JobStatusTest1 : 981.596 ms JobStatusTest2 : 849.133 ms 2) Two functions JobStatusTest3 and JobStatusTest4 perform the same actions. But one of the function performs the same steps in the cycle. Why is the speed of the functions so incredibly much different? JobStatusTest3 : 1430.777 ms JobStatusTest4 : 2.386 ms best Arsen Arutyunyan bugreport.sql Description: Binary data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Indexes on System Table
I've got a SaaS situation where I'm using 1000+ schemas in a single database (each schema contains the same tables, just different data per tenant). I used schemas so that the shared app servers could share a connection to the single database for all schemas. Things are working fine. However, when using psql, doing \d or trying to use tab complete takes FOREVER, because it's doing a sequence scan against pg_class (which has over a million rows), and relying on pg_table_is_visible to do search_path filtering. I've figured out that if I add "nspname = ANY(current_schemas(true))" to the query psql is using, and an index to pg_class on relnamespace, the query optimizer is able to do an index scan, and the queries return in milliseconds instead of minutes. However, I can't actually create an index on pg_class because it is a system table (I was able to test by copying it to a temporary table and adding the index there). My question is if there is a way to create the index on the system table somehow for just my database, and if not how would the developer community react to the suggestion of adding an index to a system table in the default postgres distro. Thanks, Cody Cutrer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
On 20.1.2012 19:47, Matt Dew wrote: > On 01/13/2012 02:49 PM, Tomas Vondra wrote: >> On 13.1.2012 22:20, Tom Lane wrote: >>> Matt Dew writes: An interesting sidenote we realized. the nice system shutdown script /etc/init.d/postgres doesn't actually wait for the db to be down, it just waits for pg_ctl to return. >>> >>> By default, "pg_ctl stop" does wait for the server to shut down ... >> >> Not really. It waits for up to 60 seconds and if the shutdown was not >> successful (as there was a connected client), it prints a message to >> the log >> >> pg_ctl: server does not shut down >> HINT: The "-m fast" option immediately disconnects sessions >> rather than >> >> and returns 1. >> >> If you really need to wait for shutdown, you need to add "-w" to the >> command line, use "-m fast" or "-m immediate". >> >> But even ignoring the return value should not cause corruption IMHO. > > Thanks Tom and Tomas, > I remember -w now, but I'd long forgotten about it. > > If the pg_ctl returns a 1 but the machine physically powers off, there > is a chance for corruption though right? Postgres is trying to write > stuff to disk and clean up and BAM power goes out. ? > > There is a chance for corruption though if the machine physically powers > off after the pg_ctl return There are various types of corruption. If you power off the system before the database properly shuts down, the data files will be corrupted. But this should be fixed on the next database startup - the database should find out it was not switched properly and perform a recovery (replay the WAL logs). So yes, it would be corrupted but fixed on the next startup. And thus should not cause issues like the one you describe. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
On 01/13/2012 02:49 PM, Tomas Vondra wrote: On 13.1.2012 22:20, Tom Lane wrote: Matt Dew writes: An interesting sidenote we realized. the nice system shutdown script /etc/init.d/postgres doesn't actually wait for the db to be down, it just waits for pg_ctl to return. By default, "pg_ctl stop" does wait for the server to shut down ... Not really. It waits for up to 60 seconds and if the shutdown was not successful (as there was a connected client), it prints a message to the log pg_ctl: server does not shut down HINT: The "-m fast" option immediately disconnects sessions rather than and returns 1. If you really need to wait for shutdown, you need to add "-w" to the command line, use "-m fast" or "-m immediate". But even ignoring the return value should not cause corruption IMHO. Thanks Tom and Tomas, I remember -w now, but I'd long forgotten about it. If the pg_ctl returns a 1 but the machine physically powers off, there is a chance for corruption though right? Postgres is trying to write stuff to disk and clean up and BAM power goes out. ? There is a chance for corruption though if the machine physically powers off after the pg_ctl return -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
On 13.1.2012 22:20, Tom Lane wrote: > Matt Dew writes: >> An interesting sidenote we realized. the nice system shutdown script >> /etc/init.d/postgres doesn't actually wait for the db to be down, it >> just waits for pg_ctl to return. > > By default, "pg_ctl stop" does wait for the server to shut down ... Not really. It waits for up to 60 seconds and if the shutdown was not successful (as there was a connected client), it prints a message to the log pg_ctl: server does not shut down HINT: The "-m fast" option immediately disconnects sessions rather than and returns 1. If you really need to wait for shutdown, you need to add "-w" to the command line, use "-m fast" or "-m immediate". But even ignoring the return value should not cause corruption IMHO. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
Matt Dew writes: > An interesting sidenote we realized. the nice system shutdown script > /etc/init.d/postgres doesn't actually wait for the db to be down, it > just waits for pg_ctl to return. By default, "pg_ctl stop" does wait for the server to shut down ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
On 01/12/2012 01:21 PM, Tom Lane wrote: Matt Dew writes: On 01/11/2012 04:29 PM, Tom Lane wrote: What exactly is your definition of a "clean shutdown"? Is a reboot command considered a clean shutdown? It's a redhat box which called /etc/init.d/postgresql stop, which does: pg_ctl stop -D '$PGDATA' -s -m fast Well, a fast-mode stop would abort the reindex operation, but that should certainly have left the catalog entries in the same state as before, so there's no obvious reason here why the indexes would've stopped being used. We're using v8.3.9 That's a tad old. Please consult http://www.postgresql.org/docs/8.3/static/release.html for reasons why an update might be a good idea. I don't recall any 8.3.x bugs that might be related to this, but I haven't trawled the commit logs to see what I've forgotten, either. I'm in a rabbit hole. I dug in more and learned that that problem may have existed before the shutdown. I believe the root problem is still the same though; having to recreate the table to get it to use indexes. Hmm. If that's the case then we don't have to explain how an aborted reindex operation could have affected the usability of the old indexes, so I'm inclined to believe that it didn't. Which seems to mean that you have a garden variety "why won't the planner use my index" issue, not something unusual. If you no longer have the original table then it may be impossible to investigate further; but if you can recreate the state where it's not using the index(es), please see http://wiki.postgresql.org/wiki/Slow_Query_Questions and pursue the issue on pgsql-performance. regards, tom lane Thanks Tom. An interesting sidenote we realized. the nice system shutdown script /etc/init.d/postgres doesn't actually wait for the db to be down, it just waits for pg_ctl to return. I'm guessing it's not good when the box shuts down before postgres is. Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
On 01/12/2012 01:21 PM, Tom Lane wrote: Matt Dew writes: On 01/11/2012 04:29 PM, Tom Lane wrote: What exactly is your definition of a "clean shutdown"? Is a reboot command considered a clean shutdown? It's a redhat box which called /etc/init.d/postgresql stop, which does: pg_ctl stop -D '$PGDATA' -s -m fast Well, a fast-mode stop would abort the reindex operation, but that should certainly have left the catalog entries in the same state as before, so there's no obvious reason here why the indexes would've stopped being used. We're using v8.3.9 That's a tad old. Please consult http://www.postgresql.org/docs/8.3/static/release.html for reasons why an update might be a good idea. I don't recall any 8.3.x bugs that might be related to this, but I haven't trawled the commit logs to see what I've forgotten, either. I'm in a rabbit hole. I dug in more and learned that that problem may have existed before the shutdown. I believe the root problem is still the same though; having to recreate the table to get it to use indexes. Hmm. If that's the case then we don't have to explain how an aborted reindex operation could have affected the usability of the old indexes, so I'm inclined to believe that it didn't. Which seems to mean that you have a garden variety "why won't the planner use my index" issue, not something unusual. If you no longer have the original table then it may be impossible to investigate further; but if you can recreate the state where it's not using the index(es), please see http://wiki.postgresql.org/wiki/Slow_Query_Questions and pursue the issue on pgsql-performance. Thanks Tom. I have the original database stored away for investigation. This was a serious problem so we're investigatng how to prevent this in the future. It's strange because even though it looks like this problem did happen before the reboot, it was once in a while. After the reboot it was everytime and the application completely stopped working. Plus after the reboot even a simple query against the table: select * from tbl where id=1; was/is doing sequential scans. It's a smallish table, just under 5 million rows. Thanks for the link. I'm using that. It's on a netapp if that matters. (Not my choice.) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
Matt Dew writes: > On 01/11/2012 04:29 PM, Tom Lane wrote: >> What exactly is your definition of a "clean shutdown"? > Is a reboot command considered a clean shutdown? It's a redhat box > which called /etc/init.d/postgresql stop, which does: pg_ctl stop -D > '$PGDATA' -s -m fast Well, a fast-mode stop would abort the reindex operation, but that should certainly have left the catalog entries in the same state as before, so there's no obvious reason here why the indexes would've stopped being used. > We're using v8.3.9 That's a tad old. Please consult http://www.postgresql.org/docs/8.3/static/release.html for reasons why an update might be a good idea. I don't recall any 8.3.x bugs that might be related to this, but I haven't trawled the commit logs to see what I've forgotten, either. > I'm in a rabbit hole. I dug in more and learned that that problem may > have existed before the shutdown. I believe the root problem is still > the same though; having to recreate the table to get it to use indexes. Hmm. If that's the case then we don't have to explain how an aborted reindex operation could have affected the usability of the old indexes, so I'm inclined to believe that it didn't. Which seems to mean that you have a garden variety "why won't the planner use my index" issue, not something unusual. If you no longer have the original table then it may be impossible to investigate further; but if you can recreate the state where it's not using the index(es), please see http://wiki.postgresql.org/wiki/Slow_Query_Questions and pursue the issue on pgsql-performance. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
On 01/11/2012 04:29 PM, Tom Lane wrote: Matt Dew writes: I have a database that was shut down, cleanly, during an 'reindex table' command. When the database came back up, queries against that table started doing sequential scans instead of using the indexes as they had been up until that point. What exactly is your definition of a "clean shutdown"? At the very least you'd have had to abort the session running the reindex. Also, what PG version is this, and what are the index definitions? Is a reboot command considered a clean shutdown? It's a redhat box which called /etc/init.d/postgresql stop, which does: pg_ctl stop -D '$PGDATA' -s -m fast We're using v8.3.9 "idx1" UNIQUE, btree (id) "idx_2" btree (homeaddress) "idx_3" btree (f3) "idx_4" btree (lower(firstname::text) varchar_pattern_ops) "idx_5" btree (lower(lastname::text) varchar_pattern_ops) "idx_6" btree (lower(lastname::text) varchar_pattern_ops, lower(firstname::text) varchar_pattern_ops, id, f5) "idx_7" btree (s2id) "idx_8" btree (sid, lower(memberusername::text) varchar_pattern_ops, lower(email::text) varchar_pattern_ops, birthdate) "idx_9" btree (id, f5) WHERE f5 = false I'm in a rabbit hole. I dug in more and learned that that problem may have existed before the shutdown. I believe the root problem is still the same though; having to recreate the table to get it to use indexes. thanks for any help, Matt regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
Matt Dew writes: > I have a database that was shut down, cleanly, during an 'reindex > table' command. When the database came back up, queries against that > table started doing sequential scans instead of using the indexes as > they had been up until that point. What exactly is your definition of a "clean shutdown"? At the very least you'd have had to abort the session running the reindex. Also, what PG version is this, and what are the index definitions? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
On 01/11/2012 11:07 AM, Scott Marlowe wrote: On Wed, Jan 11, 2012 at 10:42 AM, Matt Dew wrote: Hello all, I have a database that was shut down, cleanly, during an 'reindex table' command. When the database came back up, queries against that table started doing sequential scans instead of using the indexes as they had been up until that point. We tried: 1) vacuuming the table (vacuum tblName) 2) reindexing the table (reindex table tblName) 3) dropping and recreating the indexes but none of those actions helped. We ended up recreating the table by renaming the table and doing a create table as select * from oldTable and readding the indexes. This worked. This problem presented itself as an application timing out. It took several people, several hours to track this down and solve it. Several months ago I had two other tables also stopped using their indexes. Those times however I don't know if a database shutdown caused the problem. Has anyone had this problem? If so, what specifically is the cause? Is shutting down a database during a table rebuild or vacuum an absolute no-no? Any and all help or insight would be appreciated, Matt You likely had an invalid index, I've seen that crop up when doing a create index concurrently. Just a guess. What did or does \d of the table and its indexes show? Look for invalid in the output. Hi Scott, The output of \d looked normal. Nothing weird or different than before. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes no longer used after shutdown during reindexing
On Wed, Jan 11, 2012 at 10:42 AM, Matt Dew wrote: > Hello all, > I have a database that was shut down, cleanly, during an 'reindex table' > command. When the database came back up, queries against that table > started doing sequential scans instead of using the indexes as they had been > up until that point. > > We tried: > 1) vacuuming the table (vacuum tblName) > 2) reindexing the table (reindex table tblName) > 3) dropping and recreating the indexes > > but none of those actions helped. We ended up recreating the table by > renaming the table and doing a create table as select * from oldTable and > readding the indexes. This worked. > > This problem presented itself as an application timing out. It took several > people, several hours to track this down and solve it. > > Several months ago I had two other tables also stopped using their indexes. > Those times however I don't know if a database shutdown caused the problem. > > Has anyone had this problem? If so, what specifically is the cause? Is > shutting down a database during a table rebuild or vacuum an absolute no-no? > > Any and all help or insight would be appreciated, > Matt You likely had an invalid index, I've seen that crop up when doing a create index concurrently. Just a guess. What did or does \d of the table and its indexes show? Look for invalid in the output. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] indexes no longer used after shutdown during reindexing
Hello all, I have a database that was shut down, cleanly, during an 'reindex table' command. When the database came back up, queries against that table started doing sequential scans instead of using the indexes as they had been up until that point. We tried: 1) vacuuming the table (vacuum tblName) 2) reindexing the table (reindex table tblName) 3) dropping and recreating the indexes but none of those actions helped. We ended up recreating the table by renaming the table and doing a create table as select * from oldTable and readding the indexes. This worked. This problem presented itself as an application timing out. It took several people, several hours to track this down and solve it. Several months ago I had two other tables also stopped using their indexes. Those times however I don't know if a database shutdown caused the problem. Has anyone had this problem? If so, what specifically is the cause? Is shutting down a database during a table rebuild or vacuum an absolute no-no? Any and all help or insight would be appreciated, Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes and tables
On Sunday, December 18, 2011 04:00:14 PM amit sehas wrote: > Yes i was trying to determine how to make a View work in this situation. > From reading the details on PostgreSQL Views are not persistent, ie they > are just a SQL query short hand rather than actually creating any physical > entity backing it (i would imagine that creating such a physical backing > would be inordinately difficult to keep updated given the arbitrary > original query that was utilized to define it...)... > The views are not materialized. But the SQL they execute does make use of existing indexes. > Is there some way inheritence can play a part in this such as > > a) define a base table with 3 fields and place indexes on each one of the >3 fields > > b) define the 10 types in which we just override the name of each one of > the 3 fields (i do not know if there is such a concept as overriding the > name of a field...) perhaps a computed field which depends upon the field > inherited... > > that is as close as i could think of how to solve such as issue...but i am > not sure how inheritance works in POstgres... Inheritance will not help you with columns with different names. And indexes aren't inherited anyway. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes and tables
Hi Amit, Have you maybe tested what David J has suggested? In other words it is: • Create 10 tables with their columns and indexes on each t1(a1,a2,a3) t2(b1,b2,b3) . . . t10(n1,n2,n3) •Create VIEW with union all 10 tables SELECT a1 AS f1, a2 as f2, a3 as f3 from t1 Union SELECT b1 as f1, b2 as f2, b3 as f3 from t2 UNION (next 8 tables) Select (star) from myview where f1 (equal) 'something' (sorry, i just found out i do not have "star" and "equal" keys on my wp7) Should use index on each table for its corresponding column..( a1 to n1) Sent from my Windows Phone From: amit sehas Sent: 19 December 2011 06:17 To: pgsql-general@postgresql.org; David Johnston Subject: Re: [GENERAL] indexes and tables Yes i was trying to determine how to make a View work in this situation. From reading the details on PostgreSQL Views are not persistent, ie they are just a SQL query short hand rather than actually creating any physical entity backing it (i would imagine that creating such a physical backing would be inordinately difficult to keep updated given the arbitrary original query that was utilized to define it...)... Is there some way inheritence can play a part in this such as a) define a base table with 3 fields and place indexes on each one of the 3 fields b) define the 10 types in which we just override the name of each one of the 3 fields (i do not know if there is such a concept as overriding the name of a field...) perhaps a computed field which depends upon the field inherited... that is as close as i could think of how to solve such as issue...but i am not sure how inheritance works in POstgres... any help is greatly appreciated... thanks --- On Sun, 12/18/11, David Johnston wrote: > From: David Johnston > Subject: RE: [GENERAL] indexes and tables > To: "'amit sehas'" , pgsql-general@postgresql.org > Date: Sunday, December 18, 2011, 1:59 PM > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] > On Behalf Of amit sehas > Sent: Thursday, December 15, 2011 9:22 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] indexes and tables > > HI, > > we have a schema related question. We have 10 types of > resource records. > Each one of these resource records has 3 fields > (attributes) (lets say f1, > f2, f3)...these fields have similar meaning to the > corresponding 3 fields in > each resource record although they be named slightly > differently in each > resource record type. > > We want to view these 10 resource record types uniformly > with respect to > these 3 fields and place indexes across all the types on > each one of these > fields, so that all resource records regardless of type > would appear in > these indexes. > > We want these indexes to be stored persistently in the > physical database > because we have 10 million objects and it is not practical > to keep > recreating the indexes every time we need them. > > Is there some way to accomplish this in PostgreSQL? > > thanks > > - > > Your description is incomplete or otherwise unclear but > I'll take a stab. > > First assumption: "10 types of resource records" means you > have 10 tables > each containing three fields that have similar > semantics/meanings. > > The main thing to keep in mind that an index is always > associated with a > single table. Thus, the direct answer to your > question is that you cannot > have a single index covering all 10 tables. You will > want to create indexes > on each table and then create a VIEW that encapsulates each > of the 10 tables > with a "UNION". > > CREATE VIEW consolidated_attributes AS > SELECT f1, f2, f3 FROM table1 > UNION ALL > SELECT f1, f2, f3 FROM table1 > Etc; > > If you then issue: [ SELECT * FROM consolidated_attributes > WHERE f1 = > 'something' ] each table will have its corresponding f1 > index scanned. > > Hopefully this helps/answers your question but if not I > think you need to > provide additional detail as to what you want to accomplish > at a higher > level and not focus on whether you can create a specific > kind of index. As > index are non-logical in nature if you can provide the > logical structure of > your schema, along with the kinds of queries you wish to > write, more > specific advice can be given regarding optimization. > > David J. > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes and tables
Yes i was trying to determine how to make a View work in this situation. From reading the details on PostgreSQL Views are not persistent, ie they are just a SQL query short hand rather than actually creating any physical entity backing it (i would imagine that creating such a physical backing would be inordinately difficult to keep updated given the arbitrary original query that was utilized to define it...)... Is there some way inheritence can play a part in this such as a) define a base table with 3 fields and place indexes on each one of the 3 fields b) define the 10 types in which we just override the name of each one of the 3 fields (i do not know if there is such a concept as overriding the name of a field...) perhaps a computed field which depends upon the field inherited... that is as close as i could think of how to solve such as issue...but i am not sure how inheritance works in POstgres... any help is greatly appreciated... thanks --- On Sun, 12/18/11, David Johnston wrote: > From: David Johnston > Subject: RE: [GENERAL] indexes and tables > To: "'amit sehas'" , pgsql-general@postgresql.org > Date: Sunday, December 18, 2011, 1:59 PM > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] > On Behalf Of amit sehas > Sent: Thursday, December 15, 2011 9:22 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] indexes and tables > > HI, > > we have a schema related question. We have 10 types of > resource records. > Each one of these resource records has 3 fields > (attributes) (lets say f1, > f2, f3)...these fields have similar meaning to the > corresponding 3 fields in > each resource record although they be named slightly > differently in each > resource record type. > > We want to view these 10 resource record types uniformly > with respect to > these 3 fields and place indexes across all the types on > each one of these > fields, so that all resource records regardless of type > would appear in > these indexes. > > We want these indexes to be stored persistently in the > physical database > because we have 10 million objects and it is not practical > to keep > recreating the indexes every time we need them. > > Is there some way to accomplish this in PostgreSQL? > > thanks > > - > > Your description is incomplete or otherwise unclear but > I'll take a stab. > > First assumption: "10 types of resource records" means you > have 10 tables > each containing three fields that have similar > semantics/meanings. > > The main thing to keep in mind that an index is always > associated with a > single table. Thus, the direct answer to your > question is that you cannot > have a single index covering all 10 tables. You will > want to create indexes > on each table and then create a VIEW that encapsulates each > of the 10 tables > with a "UNION". > > CREATE VIEW consolidated_attributes AS > SELECT f1, f2, f3 FROM table1 > UNION ALL > SELECT f1, f2, f3 FROM table1 > Etc; > > If you then issue: [ SELECT * FROM consolidated_attributes > WHERE f1 = > 'something' ] each table will have its corresponding f1 > index scanned. > > Hopefully this helps/answers your question but if not I > think you need to > provide additional detail as to what you want to accomplish > at a higher > level and not focus on whether you can create a specific > kind of index. As > index are non-logical in nature if you can provide the > logical structure of > your schema, along with the kinds of queries you wish to > write, more > specific advice can be given regarding optimization. > > David J. > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes and tables
I've shown you how to make a proper VIEW that will accomplish what you want (i.e., 9 "UNION ALL" between 10 tables). Yes, the VIEW is simply a re-write RULE but the indexes on the corresponding tables are still very much real and "should" be used to when you make use of the VIEW over one of the tables. It may be that such a VIEW cannot use the indexes but unless you or someone else tells me it cannot then I am going to assume that it does (I do not have time to test it myself at the moment). You need to provide more details on what you are logically trying to accomplish, and the restrictions you face, and forget about "indexes" for the time being. If you can provide a functional, self-contained, description then maybe someone will be able to provide performance improvement suggestions. At the moment it is still unclear what your actual requirements are. I am unsure if/how inheritance would fit in but I do know that it requires that you have the same field name(s) in ALL child tables. Column names are fundamental (i.e., non-dynamic) and so if you truly need to deal with multiple names for the "same column" you will need to deal with multiple views/queries. David J. -Original Message- From: amit sehas [mailto:cu...@yahoo.com] Sent: Sunday, December 18, 2011 7:00 PM To: pgsql-general@postgresql.org; David Johnston Subject: RE: [GENERAL] indexes and tables Yes i was trying to determine how to make a View work in this situation. >From reading the details on PostgreSQL Views are not persistent, ie they are just a SQL query short hand rather than actually creating any physical entity backing it (i would imagine that creating such a physical backing would be inordinately difficult to keep updated given the arbitrary original query that was utilized to define it...)... Is there some way inheritence can play a part in this such as a) define a base table with 3 fields and place indexes on each one of the 3 fields b) define the 10 types in which we just override the name of each one of the 3 fields (i do not know if there is such a concept as overriding the name of a field...) perhaps a computed field which depends upon the field inherited... that is as close as i could think of how to solve such as issue...but i am not sure how inheritance works in POstgres... any help is greatly appreciated... thanks --- On Sun, 12/18/11, David Johnston wrote: > From: David Johnston > Subject: RE: [GENERAL] indexes and tables > To: "'amit sehas'" , pgsql-general@postgresql.org > Date: Sunday, December 18, 2011, 1:59 PM -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] > On Behalf Of amit sehas > Sent: Thursday, December 15, 2011 9:22 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] indexes and tables > > HI, > > we have a schema related question. We have 10 types of resource > records. > Each one of these resource records has 3 fields > (attributes) (lets say f1, > f2, f3)...these fields have similar meaning to the corresponding 3 > fields in each resource record although they be named slightly > differently in each resource record type. > > We want to view these 10 resource record types uniformly with respect > to these 3 fields and place indexes across all the types on each one > of these fields, so that all resource records regardless of type would > appear in these indexes. > > We want these indexes to be stored persistently in the physical > database because we have 10 million objects and it is not practical to > keep recreating the indexes every time we need them. > > Is there some way to accomplish this in PostgreSQL? > > thanks > > - > > Your description is incomplete or otherwise unclear but I'll take a > stab. > > First assumption: "10 types of resource records" means you have 10 > tables each containing three fields that have similar > semantics/meanings. > > The main thing to keep in mind that an index is always associated with > a single table. Thus, the direct answer to your question is that you > cannot have a single index covering all 10 tables. You will want to > create indexes on each table and then create a VIEW that encapsulates > each of the 10 tables with a "UNION". > > CREATE VIEW consolidated_attributes AS SELECT f1, f2, f3 FROM table1 > UNION ALL SELECT f1, f2, f3 FROM table1 Etc; > > If you then issue: [ SELECT * FROM consolidated_attributes WHERE f1 = > 'something' ] each table will have its corresponding f1 index scanned. > > Hopefully this helps/answers your question but if not I think you need > to provide additional detail as t
Re: [GENERAL] indexes and tables
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of amit sehas Sent: Thursday, December 15, 2011 9:22 PM To: pgsql-general@postgresql.org Subject: [GENERAL] indexes and tables HI, we have a schema related question. We have 10 types of resource records. Each one of these resource records has 3 fields (attributes) (lets say f1, f2, f3)...these fields have similar meaning to the corresponding 3 fields in each resource record although they be named slightly differently in each resource record type. We want to view these 10 resource record types uniformly with respect to these 3 fields and place indexes across all the types on each one of these fields, so that all resource records regardless of type would appear in these indexes. We want these indexes to be stored persistently in the physical database because we have 10 million objects and it is not practical to keep recreating the indexes every time we need them. Is there some way to accomplish this in PostgreSQL? thanks - Your description is incomplete or otherwise unclear but I'll take a stab. First assumption: "10 types of resource records" means you have 10 tables each containing three fields that have similar semantics/meanings. The main thing to keep in mind that an index is always associated with a single table. Thus, the direct answer to your question is that you cannot have a single index covering all 10 tables. You will want to create indexes on each table and then create a VIEW that encapsulates each of the 10 tables with a "UNION". CREATE VIEW consolidated_attributes AS SELECT f1, f2, f3 FROM table1 UNION ALL SELECT f1, f2, f3 FROM table1 Etc; If you then issue: [ SELECT * FROM consolidated_attributes WHERE f1 = 'something' ] each table will have its corresponding f1 index scanned. Hopefully this helps/answers your question but if not I think you need to provide additional detail as to what you want to accomplish at a higher level and not focus on whether you can create a specific kind of index. As index are non-logical in nature if you can provide the logical structure of your schema, along with the kinds of queries you wish to write, more specific advice can be given regarding optimization. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] indexes and tables
HI, we have a schema related question. We have 10 types of resource records. Each one of these resource records has 3 fields (attributes) (lets say f1, f2, f3)...these fields have similar meaning to the corresponding 3 fields in each resource record although they be named slightly differently in each resource record type. We want to view these 10 resource record types uniformly with respect to these 3 fields and place indexes across all the types on each one of these fields, so that all resource records regardless of type would appear in these indexes. We want these indexes to be stored persistently in the physical database because we have 10 million objects and it is not practical to keep recreating the indexes every time we need them. Is there some way to accomplish this in PostgreSQL? thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes not allowed on (read-only) views: Why?
Craig Ringer, 17.09.2011 02:28: On 09/17/2011 05:47 AM, Stefan Keller wrote: A (read-only) view should behave like a table, right? CREATE INDEX t1_idx ON t1 (rem); ERROR: »v1« not a table SQL state: 42809 => Why should'nt it be possible to create indexes on views in PG? It's not so much that it's not allowed, as that it's not implemented and not very practical for non-materialized views. AFAIK: "indexed view" is simply Microsoft's term for "materialized view" Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes not allowed on (read-only) views: Why?
Hi Craig, Thank you for the explanations. 2011/9/17 Craig Ringer : > It sounds like they probably use a materialized view, possibly stored as an > index-oriented table. That'd be a cool thing to support, but if done that > way would require TWO new major features PostgreSQL doesn't have. Just to understand you saying "two new major features": 1. native support for materialized views, 2. indexed (materialized) views, right? What do you mean by "index-oriented table"? (see my other thread about "Index containing data values instead of pointers to data?") Stefan 2011/9/17 Craig Ringer : > On 09/17/2011 05:47 AM, Stefan Keller wrote: >> >> A (read-only) view should behave like a table, right? >> >>> CREATE INDEX t1_idx ON t1 (rem); >> >> ERROR: »v1« not a table >> SQL state: 42809 >> >> => Why should'nt it be possible to create indexes on views in PG? > > It's not so much that it's not allowed, as that it's not implemented and not > very practical for non-materialized views. > > A normal (non-materialized) view doesn't have any data of its own, it pulls > it from one or more other tables on the fly during query execution. The > execution of a view is kind of similar to a set-returning function or a > subquery, almost as if you'd substituted the view definition into the > original query. > > That means that the view will use any indexes on the original table(s), but > there isn't really even an opportunity to check for indexes on the view its > self because the view's definition is effectively substituted into the > query. If the view definition is complex enough that it does a lot of work > where indexes on the original table(s) don't help, that work has to be done > every time. > > It only really makes sense to have indexes on materialized views. PostgreSQL > doesn't have any native support for materialized views, so it doesn't > support indexes on views. > > What you *CAN* do is use triggers to maintain your own materialized views as > regular tables, and have indexes on the tables you maintain using triggers. > This is widely discussed on the mailing list and isn't hard to do, though > it's tricky to make updates perform well with some kinds of materialized > view query. > >> >> And there is no practical reason since SQL Server can do it! See >> "Creating Indexes on Views" >> http://msdn.microsoft.com/en-us/library/aa933124%28v=sql.80%29.aspx > > It sounds like they probably use a materialized view, possibly stored as an > index-oriented table. That'd be a cool thing to support, but if done that > way would require TWO new major features PostgreSQL doesn't have. > > -- > Craig Ringer > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes not allowed on (read-only) views: Why?
On 09/17/2011 05:47 AM, Stefan Keller wrote: A (read-only) view should behave like a table, right? CREATE INDEX t1_idx ON t1 (rem); ERROR: »v1« not a table SQL state: 42809 => Why should'nt it be possible to create indexes on views in PG? It's not so much that it's not allowed, as that it's not implemented and not very practical for non-materialized views. A normal (non-materialized) view doesn't have any data of its own, it pulls it from one or more other tables on the fly during query execution. The execution of a view is kind of similar to a set-returning function or a subquery, almost as if you'd substituted the view definition into the original query. That means that the view will use any indexes on the original table(s), but there isn't really even an opportunity to check for indexes on the view its self because the view's definition is effectively substituted into the query. If the view definition is complex enough that it does a lot of work where indexes on the original table(s) don't help, that work has to be done every time. It only really makes sense to have indexes on materialized views. PostgreSQL doesn't have any native support for materialized views, so it doesn't support indexes on views. What you *CAN* do is use triggers to maintain your own materialized views as regular tables, and have indexes on the tables you maintain using triggers. This is widely discussed on the mailing list and isn't hard to do, though it's tricky to make updates perform well with some kinds of materialized view query. And there is no practical reason since SQL Server can do it! See "Creating Indexes on Views" http://msdn.microsoft.com/en-us/library/aa933124%28v=sql.80%29.aspx It sounds like they probably use a materialized view, possibly stored as an index-oriented table. That'd be a cool thing to support, but if done that way would require TWO new major features PostgreSQL doesn't have. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes not allowed on (read-only) views: Why?
On Fri, Sep 16, 2011 at 4:47 PM, Stefan Keller wrote: > A (read-only) view should behave like a table, right? > >> CREATE INDEX t1_idx ON t1 (rem); > ERROR: »v1« not a table > SQL state: 42809 > > => Why should'nt it be possible to create indexes on views in PG? > > An index on a view can speed up access to the tuples underlying. And > "indexed views" could be a method of storing the result set of the > view in the database, thereby reducing the overhead of dynamically > building the result set. An "indexed view" should automatically adapt > modifications made to the data in the base tables. So, there is some > overhead here, but this is ok when speed in retrieving results > outweighs the cost... > > And there is no practical reason since SQL Server can do it! See > "Creating Indexes on Views" > http://msdn.microsoft.com/en-us/library/aa933124%28v=sql.80%29.aspx implementation across databases is different. in postgres, views are essentially macros, thus there is no data to index. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Indexes not allowed on (read-only) views: Why?
A (read-only) view should behave like a table, right? > CREATE INDEX t1_idx ON t1 (rem); ERROR: »v1« not a table SQL state: 42809 => Why should'nt it be possible to create indexes on views in PG? An index on a view can speed up access to the tuples underlying. And "indexed views" could be a method of storing the result set of the view in the database, thereby reducing the overhead of dynamically building the result set. An "indexed view" should automatically adapt modifications made to the data in the base tables. So, there is some overhead here, but this is ok when speed in retrieving results outweighs the cost... And there is no practical reason since SQL Server can do it! See "Creating Indexes on Views" http://msdn.microsoft.com/en-us/library/aa933124%28v=sql.80%29.aspx Stefan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on inheriting tables
2011/8/24 Ondrej Ivanič > Hi, > > On 25 August 2011 11:17, Toby Corkindale > wrote: > > Do I need to make sure I re-create every index on every child table I > > create? > > That would be.. annoying, at best. > > Yes, it is little bit annoying but I like it. You don't need any index > on parent table but you have to create them "manually". I wrote simple > python script which creates partitions and required indexes in advance > (tables are partitioned by date). > > I like the flexibility because you can have different indexex on > different partitions. For example, I discovered that adding index will > improve several queries. In the production I can't afford exclusive > lock (build index concurrently takes ages) so I updated and re-run the > script which re-created future partitions. > My Personal favorite is the LIKE syntax: CREATE TABLE foo_1 (LIKE foo including indexes ) inherits (foo); It doesn't help you change children after the fact, but your new partitions automatically get whatever indexes you've stuck on the master table. --Scott > > -- > Ondrej Ivanic > (ondrej.iva...@gmail.com) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Indexes on inheriting tables
Hi, On 25 August 2011 11:17, Toby Corkindale wrote: > Do I need to make sure I re-create every index on every child table I > create? > That would be.. annoying, at best. Yes, it is little bit annoying but I like it. You don't need any index on parent table but you have to create them "manually". I wrote simple python script which creates partitions and required indexes in advance (tables are partitioned by date). I like the flexibility because you can have different indexex on different partitions. For example, I discovered that adding index will improve several queries. In the production I can't afford exclusive lock (build index concurrently takes ages) so I updated and re-run the script which re-created future partitions. -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on inheriting tables
On 25/08/11 12:10, Shoaib Mir wrote: On Thu, Aug 25, 2011 at 11:57 AM, Toby Corkindale mailto:toby.corkind...@strategicdata.com.au>> wrote: It seems messy to inherit the columns but not the indexes or checks upon them :( Yes it can be a bit annoying at timse but you can try to automate the whole process as well. Like I found this blog entry --> http://blog.inovia.fr/auto-partitioning-on-postgresql-part-1/ As far as I could tell, that isn't automating anything to do with indexes or checks on the child tables? Although using a trigger to automate the creation of the child table itself is still nifty. I hadn't thought of that. thanks, Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on inheriting tables
On Thu, Aug 25, 2011 at 11:57 AM, Toby Corkindale < toby.corkind...@strategicdata.com.au> wrote: > > It seems messy to inherit the columns but not the indexes or checks upon > them :( > > > Yes it can be a bit annoying at timse but you can try to automate the whole process as well. Like I found this blog entry --> http://blog.inovia.fr/auto-partitioning-on-postgresql-part-1/ cheers, Shoaib
Re: [GENERAL] Indexes on inheriting tables
On 25/08/11 11:34, Shoaib Mir wrote: On Thu, Aug 25, 2011 at 11:17 AM, Toby Corkindale mailto:toby.corkind...@strategicdata.com.au>> wrote: Do I need to make sure I re-create every index on every child table I create? That would be.. annoying, at best. Is there a way to enable inheritance of indexes too? You do not need an index on the master table so create indexes on child tables only. This way you are avoiding big index scans and only looking at the index on the data you are interested in (child table). Ah, but I'd rather not maintain all those extra indexes! Say I want to add a new index - now I'd need to go through and add it to a hundred tables! I guess I can script it.. but it doesn't seem good. It seems messy to inherit the columns but not the indexes or checks upon them :( Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on inheriting tables
On Thu, Aug 25, 2011 at 11:17 AM, Toby Corkindale < toby.corkind...@strategicdata.com.au> wrote: > > Do I need to make sure I re-create every index on every child table I > create? > That would be.. annoying, at best. > > Is there a way to enable inheritance of indexes too? > > You do not need an index on the master table so create indexes on child tables only. This way you are avoiding big index scans and only looking at the index on the data you are interested in (child table). cheers, Shoaib
[GENERAL] Indexes on inheriting tables
Hi, I'm using Pg 9.0 and inheritance to do table partitioning. A simple example would be: CREATE TABLE foo ( id INTEGER PRIMARY KEY, thing VARCHAR(32) ); CREATE INDEX foo_thing_idx ON foo(thing); CREATE TABLE foo_1 () INHERITS (foo); I read that foreign key constraints wouldn't be inherited. However I am also finding that indexes aren't inherited either. eg. EXPLAIN SELECT id FROM foo WHERE thing='something'; will indicate that an indexed scan will be done over foo, followed by a sequential scan over foo_1. Do I need to make sure I re-create every index on every child table I create? That would be.. annoying, at best. Is there a way to enable inheritance of indexes too? Cheers, Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on individual columns of composite primary key
"Apparently (after reading the documentation link provided by Tom) there can be value to indexes when accessed with leading columns missing". That is a new one on me too - interesting. I suppose it comes down to testing at the end of the day - if you "set enable_seqscan to false" and "EXPLAIN ANALYSE" your query then you will see whether the indexes you create are used. Whether they are useful will require you to set enable_seqscan back to true and see whether the optimizer chooses to use them (but that will change as your data does).
Re: [GENERAL] Indexes on individual columns of composite primary key
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dann Corbit Sent: Monday, November 15, 2010 12:21 PM To: 'Dan Halbert'; pgsql-general@postgresql.org Subject: Re: [GENERAL] Indexes on individual columns of composite primary key From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dan Halbert Sent: Monday, November 15, 2010 12:01 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Indexes on individual columns of composite primary key I have a table with four columns. Three of those columns are defined as the composite primary key. Does it make sense to create indexes on any or all of those three columns individually for performance reasons? PG does let me create the indexes. But perhaps it's redundant, since there's an implicitly-created index for the composite primary key. >> It depends upon your access patterns. Consider a key on: CREATE UNIQUE INDEX fourcols ON mytable(col1, col2, col3, col4); If you always query in this way: SELECT * FROM mytable WHERE col1 = ?, col2 = ?, col3 = ?, col4 = ? Or possibly: SELECT * FROM mytable WHERE col1 = ?, col2 = ?, col3 = ? Or possibly: SELECT * FROM mytable WHERE col1 = ?, col2 = ? Or possibly: SELECT * FROM mytable WHERE col1 = ? Then there is no value in creating an index on the other columns. If (on the other hand) you often query like this: SELECT * FROM mytable WHERE col2 = ? Or possibly: SELECT * FROM mytable WHERE col4 = ? Then it makes sense to create an index on col2 and an index on col4. The composite index will remain useful as long as the most significant columns are provided. << Apparently (after reading the documentation link provided by Tom) there can be value to indexes when accessed with leading columns missing. Aside: I think there is a possible bug in the documentation. This link: http://www.postgresql.org/docs/9.0/static/indexes-unique.html Says that only btree indexes can be unique. But Hash indexes seem an obvious exception to me, or am I overlooking something?
Re: [GENERAL] Indexes on individual columns of composite primary key
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dan Halbert Sent: Monday, November 15, 2010 12:01 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Indexes on individual columns of composite primary key I have a table with four columns. Three of those columns are defined as the composite primary key. Does it make sense to create indexes on any or all of those three columns individually for performance reasons? PG does let me create the indexes. But perhaps it's redundant, since there's an implicitly-created index for the composite primary key. >> It depends upon your access patterns. Consider a key on: CREATE UNIQUE INDEX fourcols ON mytable(col1, col2, col3, col4); If you always query in this way: SELECT * FROM mytable WHERE col1 = ?, col2 = ?, col3 = ?, col4 = ? Or possibly: SELECT * FROM mytable WHERE col1 = ?, col2 = ?, col3 = ? Or possibly: SELECT * FROM mytable WHERE col1 = ?, col2 = ? Or possibly: SELECT * FROM mytable WHERE col1 = ? Then there is no value in creating an index on the other columns. If (on the other hand) you often query like this: SELECT * FROM mytable WHERE col2 = ? Or possibly: SELECT * FROM mytable WHERE col4 = ? Then it makes sense to create an index on col2 and an index on col4. The composite index will remain useful as long as the most significant columns are provided. <<
Re: [GENERAL] Indexes on individual columns of composite primary key
Dan, It depends on your application. There is no point in creating an index with the same 3 columns in the primary key (in the same order). If you have an index on COL1, COL2 and COL3 (in that order) then if you have a query such as SELECT COL1, COL2, COL3 from T1 then the index will be considered. Same if you have a query with the same columns but different order ie SELECT COL2, COL1, COL3 from T1 and if you just select the first column ie SELECT COL1 from T1. The index won't be considered if you have a query such as SELECT COL2 FROM T1 so if your application does this you may wish to consider such indexes. Also bear in mind the order of which you create the index or primary key. They should be ordered by uniqueness starting with the most unique. On Mon, Nov 15, 2010 at 8:01 PM, Dan Halbert wrote: > I have a table with four columns. Three of those columns are defined as the > composite primary key. Does it make sense to create indexes on any or all of > those three columns individually for performance reasons? PG does let me > create the indexes. But perhaps it's redundant, since there's an > implicitly-created index for the composite primary key. > > > > Thanks, > > Dan >
Re: [GENERAL] Indexes on individual columns of composite primary key
"Dan Halbert" writes: > I have a table with four columns. Three of those columns are defined > as the composite primary key. Does it make sense to create indexes on > any or all of those three columns individually for performance > reasons? Please see http://www.postgresql.org/docs/9.0/static/indexes.html particularly sections 11.3 and 11.5. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Indexes on individual columns of composite primary key
I have a table with four columns. Three of those columns are defined as the composite primary key. Does it make sense to create indexes on any or all of those three columns individually for performance reasons? PG does let me create the indexes. But perhaps it's redundant, since there's an implicitly-created index for the composite primary key. Thanks, Dan
Re: [GENERAL] indexes problem
On 1/27/2010 7:32 AM, J Scanf wrote: Hi all. I have a problem with two tables of same structure: 'orders_stat_pre' and 'orders_stat_pre_new'. store=# \d orders_stat_pre Column | Type | Modifiers ++--- id | integer| not null user_name | text | category_name | character varying(10) | ctime | timestamp without timezone | Indexes: "orders_stat_pre_pkey" PRIMARY KEY, btree (id) "orders_stat_pre_user_idx" btree (user_name, category_name, ctime DESC) store=# \d orders_stat_pre_new Column | Type | Modifiers ++--- id | integer| user_name | text | category_name | character varying(10) | ctime | timestamp without timezone | Indexes: "orders_stat_pre_new_user_idx" btree (user_name, category_name, ctime DESC) I try to select last 10 orders from old table (i.e. 'orders_stat_pre'): store=# explain select * from orders_stat_pre where user_name = 'Alex' and category_name = 'Books' order by ctime desc limit 10; QUERY PLAN --- Limit (cost=0.00..40.40 rows=10 width=335) -> Index Scan using orders_stat_pre_user_idx on orders_stat_pre (cost=0.00..15505.87 rows=3838 width=335) Index Cond: ((user_name = 'Alex'::text) AND ((category_name)::text = 'Books'::text)) (3 rows) Then I do the same query on new table (i.e. 'orders_stat_pre_new'): store=# explain select * from orders_stat_pre_new where user_name = 'Alex' and category_name = 'Books' order by ctime desc limit 10; QUERY PLAN Limit (cost=1719969.83..1719969.86 rows=10 width=563) -> Sort (cost=1719969.83..1719981.08 rows=4499 width=563) Sort Key: ctime -> Seq Scan on orders_stat_pre_new (cost=0.00..1719872.61 rows=4499 width=563) Filter: ((user_name = 'Alex'::text) AND ((category_name)::text = 'Books'::text)) (5 rows) I'm confused on how can I optimize the last query? Or where I can find corresponding info. Thank you! 1) an "explain analyze" might give more useful info. 2) are your stats up do date? run 'analyze orders_stat_pre_new' and try again 3) you might be indexing too much. An index on just user_name might be enough. The extra fields (category_name and ctime) may not help you as much as you think it will. A more complicated index (multiple fields) makes it harder for PG to use. Drop that index and create one on just user_name and compare the times. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] indexes problem
Hi all. I have a problem with two tables of same structure: 'orders_stat_pre' and 'orders_stat_pre_new'. store=# \d orders_stat_pre Column | Type | Modifiers ++--- id | integer| not null user_name | text | category_name | character varying(10) | ctime | timestamp without timezone | Indexes: "orders_stat_pre_pkey" PRIMARY KEY, btree (id) "orders_stat_pre_user_idx" btree (user_name, category_name, ctime DESC) store=# \d orders_stat_pre_new Column | Type | Modifiers ++--- id | integer| user_name | text | category_name | character varying(10) | ctime | timestamp without timezone | Indexes: "orders_stat_pre_new_user_idx" btree (user_name, category_name, ctime DESC) I try to select last 10 orders from old table (i.e. 'orders_stat_pre'): store=# explain select * from orders_stat_pre where user_name = 'Alex' and category_name = 'Books' order by ctime desc limit 10; QUERY PLAN --- Limit (cost=0.00..40.40 rows=10 width=335) -> Index Scan using orders_stat_pre_user_idx on orders_stat_pre (cost=0.00..15505.87 rows=3838 width=335) Index Cond: ((user_name = 'Alex'::text) AND ((category_name)::text = 'Books'::text)) (3 rows) Then I do the same query on new table (i.e. 'orders_stat_pre_new'): store=# explain select * from orders_stat_pre_new where user_name = 'Alex' and category_name = 'Books' order by ctime desc limit 10; QUERY PLAN Limit (cost=1719969.83..1719969.86 rows=10 width=563) -> Sort (cost=1719969.83..1719981.08 rows=4499 width=563) Sort Key: ctime -> Seq Scan on orders_stat_pre_new (cost=0.00..1719872.61 rows=4499 width=563) Filter: ((user_name = 'Alex'::text) AND ((category_name)::text = 'Books'::text)) (5 rows) I'm confused on how can I optimize the last query? Or where I can find corresponding info. Thank you!
Re: [GENERAL] indexes on partitioned tables - on the base table, or the partitioned tables?
Hi Kevin, From the PG documentation (section 5.8 - inheritance): http://www.postgresql.org/docs/current/static/ddl-inherit.html "A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint." So there's no "best place", there's only one place for the indexes and constraints - which is the table(s) that contain the data, which would usually be the individual partition tables. (This caveat seems to be absent from the Partitioning documentation (section 5.9)) Cheers, ~Hrishi It was Sunday 30 August 2009 07:17:28 pm that the wise Kevin Kempter thus wrote: > Hi all; > > where's the best place for the indexes/constraints on a partitioned table. > > I assume it's best to place the FK constraints/triggers on the base/master > table and the indexes on the individual partition tables. > > Thoughts? > > Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] indexes on partitioned tables - on the base table, or the partitioned tables?
Hi all; where's the best place for the indexes/constraints on a partitioned table. I assume it's best to place the FK constraints/triggers on the base/master table and the indexes on the individual partition tables. Thoughts? Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on float8 vs integer
I wish that I didn't have to say this, but that is over my head at this point. I see this HUGE, steep mountain ahead of me and a little sign in front of it saying, "Learning Curve, start here." :-) Dennis Gearon Signature Warning EARTH has a Right To Life I agree with Bolivian President Evo Morales # The right to life: "The right for no ecosystem to be eliminated by the irresponsible acts of human beings." # The right of biosystems to regenerate themselves: "Development cannot be infinite. There's a limit on everything." # The right to a clean life: "The right for Mother Earth to live without contamination, pollution. Fish and animals and trees have rights." # The right to harmony and balance between everyone and everything: "We are all interdependent." See the movie - 'Inconvenient Truth' See the movie - 'Syriana' --- On Sun, 7/12/09, Brent Wood wrote: > From: Brent Wood > Subject: Re: [GENERAL] indexes on float8 vs integer > To: gear...@sbcglobal.net > Date: Sunday, July 12, 2009, 9:10 PM > You might look at UMN mapserver or > Geoserver to provide PostGIS data via WMS/WFS and OpenLayers > to plot these layers on top of Google Maps. These tools > facilitate this sort of online map production pretty easily, > although hosting can be an issue as teh requirements become > more specific. > > Cheers, > > Brent > > > Brent Wood > DBA/GIS consultant > NIWA, Wellington > New Zealand > >>> Dennis Gearon > 07/13/09 1:05 PM >>> > > Well, Brent, > I'm just getting started on this > design. I'm doing it at a hosting site, initially, so I have > to find out if they have or will load this module. > At first, I was just going to > interpolate the distance as a bounding box based on the > distance between latitude lines and longitude lines at that > latitude. Then serve the data based on the integers for > lat/long between two values. All the geographic calculations > would have taken place in the server app, then postgres > would only be working with integers. > So, what is the base type for the > point column? > I had planned on using google maps > as the geographic server, I was going to query them using > their API and a data set of center location and labeled > points within a certain range. > Lot's to learn here, that's for > sure. I will file your reply and look at it in a week or so > when I store the first data. > Dennis Gearon > > > > --- On Sun, 7/12/09, Brent Wood > wrote: > > > From: Brent Wood > > Subject: Re: [GENERAL] indexes on float8 vs integer > > To: gear...@sbcglobal.net > > Cc: pgsql-general@postgresql.org > > Date: Sunday, July 12, 2009, 1:52 PM > > Hi Dennis, > > > > Is there any reason you are not using PostGIS to store > the > > values as point geometries & use a spatial (GIST) > index > > on them? I have tables with hundreds of millions of > point > > features which work well. On disk data volume is not > really > > worth optimising for with such systems, i suggest > > flexibility, ease of implementation & overall > > performance should be more valuable. > > > > If you need to store & query coordinates, then a > map > > based tool seems relevant, and there are plenty of > tools to > > do this soirt of thing with PostGIS data, such as > Mapserver, > > GeoServer at the back end & OpenLayers in the > front > > end. > > > > > > Cheers, > > > > Brent Wood > > > > > > Brent Wood > > DBA/GIS consultant > > NIWA, Wellington > > New Zealand > > >>> Scott Marlowe > > 07/12/09 10:31 PM >>> > > On Sat, Jul 11, 2009 at 10:15 PM, Dennis Gearon > > wrote: > > > > > > Anyone got any insight or experience in the speed > and > > size of indexes on Integer(4 byte) vs float (8byte). > For a > > project that I'm on, I'm contemplating using an > integer > > for: > > > > > > Latitude > > > Longitude > > > > > > In a huge, publically searchable table. > > > > > > In the INSERTS, the representation would be equal > to: > > > > > > IntegerLatOrLong = > to_integer( > > float8LatOrLong * to_float(100) ); > > > > > > This would keep it in a smaller (4 bytes vs 8 > byte) > > representation with simple numeric comparison for > indexing > > values while still provide 6 decimals of precision, > i.e. > > 4.25
Re: [GENERAL] indexes on float8 vs integer
Well, Brent, I'm just getting started on this design. I'm doing it at a hosting site, initially, so I have to find out if they have or will load this module. At first, I was just going to interpolate the distance as a bounding box based on the distance between latitude lines and longitude lines at that latitude. Then serve the data based on the integers for lat/long between two values. All the geographic calculations would have taken place in the server app, then postgres would only be working with integers. So, what is the base type for the point column? I had planned on using google maps as the geographic server, I was going to query them using their API and a data set of center location and labeled points within a certain range. Lot's to learn here, that's for sure. I will file your reply and look at it in a week or so when I store the first data. Dennis Gearon --- On Sun, 7/12/09, Brent Wood wrote: > From: Brent Wood > Subject: Re: [GENERAL] indexes on float8 vs integer > To: gear...@sbcglobal.net > Cc: pgsql-general@postgresql.org > Date: Sunday, July 12, 2009, 1:52 PM > Hi Dennis, > > Is there any reason you are not using PostGIS to store the > values as point geometries & use a spatial (GIST) index > on them? I have tables with hundreds of millions of point > features which work well. On disk data volume is not really > worth optimising for with such systems, i suggest > flexibility, ease of implementation & overall > performance should be more valuable. > > If you need to store & query coordinates, then a map > based tool seems relevant, and there are plenty of tools to > do this soirt of thing with PostGIS data, such as Mapserver, > GeoServer at the back end & OpenLayers in the front > end. > > > Cheers, > > Brent Wood > > > Brent Wood > DBA/GIS consultant > NIWA, Wellington > New Zealand > >>> Scott Marlowe > 07/12/09 10:31 PM >>> > On Sat, Jul 11, 2009 at 10:15 PM, Dennis Gearon > wrote: > > > > Anyone got any insight or experience in the speed and > size of indexes on Integer(4 byte) vs float (8byte). For a > project that I'm on, I'm contemplating using an integer > for: > > > > Latitude > > Longitude > > > > In a huge, publically searchable table. > > > > In the INSERTS, the representation would be equal to: > > > > IntegerLatOrLong = to_integer( > float8LatOrLong * to_float(100) ); > > > > This would keep it in a smaller (4 bytes vs 8 byte) > representation with simple numeric comparison for indexing > values while still provide 6 decimals of precision, i.e. > 4.25 inches of resolution, what google mapes provides. > > > > I am expecting this table to be very huge. Hey, I want > to be the next 'portal' :-) > > Dennis Gearon > > Well, floats can be bad if you need exact math or matching > anyway, and > math on them is generally slower than int math. OTOH, > you could look > into numeric to see if it does what you want. Used to > be way slower > than int, but in recent versions of pgsql it's gotten much > faster. > Numeric is exact, where float is approximate, so if having > exact > values be stored is important, then either using int and > treating it > like fixed point, or using numeric is usually better. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > NIWA is the trading name of the National Institute of Water > & Atmospheric Research Ltd. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on float8 vs integer
Hi Dennis, Is there any reason you are not using PostGIS to store the values as point geometries & use a spatial (GIST) index on them? I have tables with hundreds of millions of point features which work well. On disk data volume is not really worth optimising for with such systems, i suggest flexibility, ease of implementation & overall performance should be more valuable. If you need to store & query coordinates, then a map based tool seems relevant, and there are plenty of tools to do this soirt of thing with PostGIS data, such as Mapserver, GeoServer at the back end & OpenLayers in the front end. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Scott Marlowe 07/12/09 10:31 PM >>> On Sat, Jul 11, 2009 at 10:15 PM, Dennis Gearon wrote: > > Anyone got any insight or experience in the speed and size of indexes on > Integer(4 byte) vs float (8byte). For a project that I'm on, I'm > contemplating using an integer for: > > Latitude > Longitude > > In a huge, publically searchable table. > > In the INSERTS, the representation would be equal to: > > IntegerLatOrLong = to_integer( float8LatOrLong * to_float(100) ); > > This would keep it in a smaller (4 bytes vs 8 byte) representation with > simple numeric comparison for indexing values while still provide 6 decimals > of precision, i.e. 4.25 inches of resolution, what google mapes provides. > > I am expecting this table to be very huge. Hey, I want to be the next > 'portal' :-) > Dennis Gearon Well, floats can be bad if you need exact math or matching anyway, and math on them is generally slower than int math. OTOH, you could look into numeric to see if it does what you want. Used to be way slower than int, but in recent versions of pgsql it's gotten much faster. Numeric is exact, where float is approximate, so if having exact values be stored is important, then either using int and treating it like fixed point, or using numeric is usually better. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on float8 vs integer
On Sat, Jul 11, 2009 at 10:15 PM, Dennis Gearon wrote: > > Anyone got any insight or experience in the speed and size of indexes on > Integer(4 byte) vs float (8byte). For a project that I'm on, I'm > contemplating using an integer for: > > Latitude > Longitude > > In a huge, publically searchable table. > > In the INSERTS, the representation would be equal to: > > IntegerLatOrLong = to_integer( float8LatOrLong * to_float(100) ); > > This would keep it in a smaller (4 bytes vs 8 byte) representation with > simple numeric comparison for indexing values while still provide 6 decimals > of precision, i.e. 4.25 inches of resolution, what google mapes provides. > > I am expecting this table to be very huge. Hey, I want to be the next > 'portal' :-) > Dennis Gearon Well, floats can be bad if you need exact math or matching anyway, and math on them is generally slower than int math. OTOH, you could look into numeric to see if it does what you want. Used to be way slower than int, but in recent versions of pgsql it's gotten much faster. Numeric is exact, where float is approximate, so if having exact values be stored is important, then either using int and treating it like fixed point, or using numeric is usually better. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] indexes on float8 vs integer
Anyone got any insight or experience in the speed and size of indexes on Integer(4 byte) vs float (8byte). For a project that I'm on, I'm contemplating using an integer for: Latitude Longitude In a huge, publically searchable table. In the INSERTS, the representation would be equal to: IntegerLatOrLong = to_integer( float8LatOrLong * to_float(100) ); This would keep it in a smaller (4 bytes vs 8 byte) representation with simple numeric comparison for indexing values while still provide 6 decimals of precision, i.e. 4.25 inches of resolution, what google mapes provides. I am expecting this table to be very huge. Hey, I want to be the next 'portal' :-) Dennis Gearon Signature Warning EARTH has a Right To Life I agree with Bolivian President Evo Morales # The right to life: "The right for no ecosystem to be eliminated by the irresponsible acts of human beings." # The right of biosystems to regenerate themselves: "Development cannot be infinite. There's a limit on everything." # The right to a clean life: "The right for Mother Earth to live without contamination, pollution. Fish and animals and trees have rights." # The right to harmony and balance between everyone and everything: "We are all interdependent." See the movie - 'Inconvenient Truth' See the movie - 'Syriana' -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on NULL's and order by ... limit N queries
Maxim Boguk <[EMAIL PROTECTED]> writes: > But why? NULL's have some special representation in index which don't work > same as normal values? In general, NULLs don't work the same as normal values, no. The reason this particular query isn't working as you are expecting is that "foo IS NULL" isn't seen as an ordering constraint by the planner's pathkey machinery, and so the query doesn't appear to match the index order. You could work around it by explicitly specifying a matching ordering: SELECT * from cluster_weight where cluster_weight.rubric_id IS NULL ORDER BY rubric_id, pos LIMIT 5; ^^ regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on NULL's and order by ... limit N queries
Alvaro Herrera wrote: Maxim Boguk wrote: Sorry with all my respect to you, you look like wrong. The difference is that the plan with -1 does not need to sort the output, because it comes sorted out of the index; so the execution can be stopped as soon as 5 tuples have come out. With NULL, that can't be done. But why? NULL's have some special representation in index which don't work same as normal values? Eg output with rubric_id is NULL dont come sorted from index? Really my tests show same behavior of -1 and NULL values: mboguk_billing=# SELECT pos from cluster_weight where rubric_id=-1 limit 20; pos - 20 20 25 40 40 50 60 60 80 80 100 120 140 160 180 200 220 240 260 280 (20 rows) mboguk_billing=# UPDATE cluster_weight set rubric_id=NULL where rubric_id=-1; UPDATE 26435 mboguk_billing=# ANALYZE cluster_weight; ANALYZE mboguk_billing=# SELECT pos from cluster_weight where rubric_id is NULL limit 20; pos - 20 20 25 40 40 50 60 60 80 80 100 120 140 160 180 200 220 240 260 280 (20 rows) Eg output with rubric_id is NULL come ordered be pos from index (rubric_id, pos) ( Here is explains: mboguk_billing=# EXPLAIN ANALYZE SELECT pos from cluster_weight where rubric_id is NULL limit 20; QUERY PLAN --- Limit (cost=0.00..0.99 rows=20 width=2) (actual time=0.050..0.144 rows=20 loops=1) -> Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..1314.94 rows=26435 width=2) (actual time=0.045..0.082 rows=20 loops=1) Index Cond: (rubric_id IS NULL) Total runtime: 0.214 ms (4 rows) mboguk_billing=# UPDATE cluster_weight set rubric_id=-1 where rubric_id IS NULL; UPDATE 26435 mboguk_billing=# ANALYZE cluster_weight; ANALYZE mboguk_billing=# EXPLAIN ANALYZE SELECT pos from cluster_weight where rubric_id=-1 limit 20; QUERY PLAN --- Limit (cost=0.00..0.95 rows=20 width=2) (actual time=0.050..0.141 rows=20 loops=1) -> Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..1259.05 rows=26435 width=2) (actual time=0.045..0.081 rows=20 loops=1) Index Cond: (rubric_id = (-1)) Total runtime: 0.214 ms (4 rows) Plans look same. ) PS: REINDEX do not change situation. -- SY Maxim Boguk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on NULL's and order by ... limit N queries
Maxim Boguk wrote: > Sorry with all my respect to you, you look like wrong. The difference is that the plan with -1 does not need to sort the output, because it comes sorted out of the index; so the execution can be stopped as soon as 5 tuples have come out. With NULL, that can't be done. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on NULL's and order by ... limit N queries
Sorry with all my respect to you, you look like wrong. Here example: With NULL's: mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id IS NULL ORDER BY pos LIMIT 5; QUERY PLAN --- Limit (cost=1503.75..1503.76 rows=5 width=28) (actual time=93.334..93.353 rows=5 loops=1) -> Sort (cost=1503.75..1569.84 rows=26435 width=28) (actual time=93.329..93.335 rows=5 loops=1) Sort Key: pos Sort Method: top-N heapsort Memory: 25kB -> Bitmap Heap Scan on cluster_weight (cost=314.32..1064.67 rows=26435 width=28) (actual time=7.519..48.678 rows=26435 loops=1) Recheck Cond: (rubric_id IS NULL) -> Bitmap Index Scan on cluster_weight_2 (cost=0.00..307.72 rows=26435 width=0) (actual time=7.350..7.350 rows=26435 loops=1) Index Cond: (rubric_id IS NULL) Total runtime: 93.433 ms (9 rows) Now lets change NULL's to -1 mboguk_billing=# UPDATE cluster_weight set rubric_id=-1 where rubric_id IS NULL; UPDATE 26435 And ANALYZE mboguk_billing=# ANALYZE cluster_weight; ANALYZE And try same query with -1 instead of NULL: mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id=-1 ORDER BY pos LIMIT 5; QUERY PLAN --- Limit (cost=0.00..0.25 rows=5 width=28) (actual time=0.056..0.080 rows=5 loops=1) -> Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..1334.41 rows=26435 width=28) (actual time=0.053..0.065 rows=5 loops=1) Index Cond: (rubric_id = (-1)) Total runtime: 0.133 ms (4 rows) And plan become normal. So issue not with too many NULL's in my dataset. -- SY, Maxim Boguk Tom Lane wrote: Maxim Boguk <[EMAIL PROTECTED]> writes: Looks like when indexed search over NULL's added, planner wasn't learned right way use such index on "where something is NULL order by ... limit ..." queries. There's nothing wrong with the plan; you've just got too many NULLs to make it worth using the index for that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on NULL's and order by ... limit N queries
Maxim Boguk <[EMAIL PROTECTED]> writes: > Looks like when indexed search over NULL's added, planner wasn't learned > right way use such index on "where something is NULL order by ... limit ..." > queries. There's nothing wrong with the plan; you've just got too many NULLs to make it worth using the index for that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Indexes on NULL's and order by ... limit N queries
When i read about 8.3 support indexed queries on NULL values (like rubric_id is NULL) i was really happy. But reality strike again... look like NULL in WHERE don't allow effective using index on (rubric_id, pos) for queries like: ... WHERE rubric_id IS NULL ORDER BY pos LIMIT 5 Here is some details about my issue (all tests on fresh loaded/analyzed into empty 8.3.5 DB): mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id=8 order by pos limit 5; QUERY PLAN --- Limit (cost=1337.02..1337.03 rows=5 width=28) (actual time=27.556..27.575 rows=5 loops=1) -> Sort (cost=1337.02..1340.77 rows=1501 width=28) (actual time=27.552..27.558 rows=5 loops=1) Sort Key: pos Sort Method: top-N heapsort Memory: 25kB -> Seq Scan on cluster_weight (cost=0.00..1312.09 rows=1501 width=28) (actual time=0.058..25.008 rows=1501 loops=1) Filter: (rubric_id = 8) Total runtime: 27.638 ms (7 rows) ok so we need index on (rubric_id, pos), lets add it: mboguk_billing=# CREATE INDEX cluster_weight_2 on cluster_weight(rubric_id, pos); CREATE INDEX And try again: mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id=8 order by pos limit 5; QUERY PLAN - Limit (cost=0.00..1.70 rows=5 width=28) (actual time=0.095..0.122 rows=5 loops=1) -> Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..509.31 rows=1501 width=28) (actual time=0.090..0.104 rows=5 loops=1) Index Cond: (rubric_id = 8) Total runtime: 0.176 ms (4 rows) Ok... so now query works as intended... Lets check are index used on search NULL values: mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id IS NULL; QUERY PLAN -- Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..1034.21 rows=26435 width=28) (actual time=0.053..48.123 rows=26435 loops=1) Index Cond: (rubric_id IS NULL) Total runtime: 85.210 ms (3 rows) Yes it is working... Now lets try main query over NULL: mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id IS NULL ORDER BY pos LIMIT 5; QUERY PLAN -- Limit (cost=1473.29..1473.30 rows=5 width=28) (actual time=92.220..92.239 rows=5 loops=1) -> Sort (cost=1473.29..1539.37 rows=26435 width=28) (actual time=92.216..92.223 rows=5 loops=1) Sort Key: pos Sort Method: top-N heapsort Memory: 25kB -> Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..1034.21 rows=26435 width=28) (actual time=0.033..47.333 rows=26435 loops=1) Index Cond: (rubric_id IS NULL) Total runtime: 92.310 ms (7 rows) Ooops... that is surprise... I wasn't ready see that plan here... and performance difference over 1000. Looks like when indexed search over NULL's added, planner wasn't learned right way use such index on "where something is NULL order by ... limit ..." queries. Thanks for any responses and sorry for not so good English. -- SY, Maxim Boguk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
Matthew Dennis wrote: Given table T(c1 int) and function F(arg int) create an index on T using F(c1). It appears that if you execute "create or replace function F" and provide a different implementation that the index still contains the results from the original implementation, thus if you execute something like "select * from T where F(c1)" after replacing the function that it now misses rows that should be returned. In other words, the index isn't aware the function is now returning different values. That's not the correct/expected behavior is it? I would have expected that replacing the function would have caused any indexes that depend on that function to be reindexed/recreated with the new function implementation. I have added a Todo item about this. But as you see, the problem is quite large and involved. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Thu, Aug 28, 2008 at 7:45 PM, Matthew Dennis <[EMAIL PROTECTED]> wrote: > Another question though. Since I could potentially start transaction, drop > indexes/checks, replace function, create indexes/checks, commit tranasaction > could I deal with the case of the constant folding into the cached plan by > flushing the entire cache in the same transaction? Is cache flushing > transactional? The cases I have for this are infrequent in time and the > overhead of reindexing things, rechecking checks/unique indexes already > dwarf the performance lost to flushing the cache. > > On a related note, if I had a maintenence window where I can shutdown all > DB access, make the referenced changes to the > functions/indexes/caches/checks and restart PG - in your opinion, are there > other likely problems to changing an immutable function under those > circumstances, or should that be pretty safe? In other words, I have a > function that has indexes on it that does the wrong thing - what do I do to > replace it? > In the thread below, we kind of got side tracked on some other stuff and I never got an answer to the questions above. Does anyone have any insight/suggestions about the best way to replace a function that is used by an index? http://groups.google.com/group/pgsql.general/browse_thread/thread/92289ef0c2f5a109/8f96fb24bdd668e8
Re: [GENERAL] indexes on functions and create or replace function
Matthew Dennis wrote: > The cases about taking a string and sending it via execute don't seem to fit > here for 1) cases where it is impossible to track the dependencies can be > trivially constructed and 2) the very nature of the execute statement makes > it obvious that it I shouldn't expect it to be tracked. Poor Analogy: In C, > if foo calls bar and I remove bar I expect the compiler to tell me. If > elsewhere in my code, I construct a memory address of where I believe bar > should be and execute it I have no expectations for the compiler to tell me > bar was removed. The analogy is poor, yes. A better analogy is the use of dlopen() on a library. If the library is not present at run time, the compiler will not tell you anything. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Thu, Aug 28, 2008 at 8:51 PM, Alvaro Herrera <[EMAIL PROTECTED]>wrote: > There's no way at all in the general case (a function name could be > passed as a parameter, for example). I think Matthew is suggesting to > track dependencies at run time, but that seems a recipe for burnt > fingers and an overall performance loss, for what seems a dubious gain. I wouldn't think that it would be that much of a performance loss (except for DDL type things where you were setting up the dependency in the first place) though you would certainly know better than me. In any case, I know it would be a lot of work and I wasn't suggesting doing it all at once, but rather as a rule of thumb going forward - a continuous improvement to be made as other work is done. If people in a better position than I to gage this really think performance would suffer a great deal or that it's not doable (or shouldn't be done), I respect that. It seems that we should at least give warnings where we can though and probably doc it somewhere. "hey user, you replaced a plpgsql function that was used in an index, your index may now be invalid. Please see deatils at xyz".
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 7:22 PM, Matthew Dennis wrote: Yes, but in the case of pluggable languages, you still load something that constitutes the "source". In the case of PL/Java, the jar for example. This would mean that, for example, if you changed any single function (no matter how distant and irrelevant to the one used to create a functional index), the jar would change, so we would have to invalidate all functional indexes written using functions contained in that jar. I'd certainly rebel at that: It would make deploying a new version of the jar very expensive, and unavoidably so. I'd have to say that the current situation falls into the category of "slightly annoying," but it has the benefit that whether or not to rebuild the index is left up to me. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Thu, Aug 28, 2008 at 9:09 PM, Christophe <[EMAIL PROTECTED]> wrote: > > On Aug 28, 2008, at 7:04 PM, Matthew Dennis wrote: > >> The plpgsql execute statement, as I understand it, means "take this string >> and execute like a client sent it to you". >> > > Of course, the string could come from anywhere. There's no inherent reason > that I can think of (except good taste) that you could not write a function > that retrieved a string out of a field in a table, executed it, and returned > that as a value for use in an index. The client didn't send the string > along, but it still creates dependencies. > > Anyway, as was correctly pointed out, tracking dependencies in PL/pgSQL is > bad enough, but PL/Java, PL/Perl... > > Actually because reading the string from a table prevents the function from being immutable (it could be stable), it could not be used in an index. However, you're right though that where the string came from is really not important. My point is that nobody would have expectations of execute tracking dependencies of the sql it executes for the same reason nobody has expectations that sql that lives in application code will have it's dependencies tracked by PG...
Re: [GENERAL] indexes on functions and create or replace function
On Thu, Aug 28, 2008 at 9:01 PM, Christophe <[EMAIL PROTECTED]> wrote: > > On Aug 28, 2008, at 6:51 PM, Alvaro Herrera wrote: > >> Also, you have to keep in mind that we support pluggable languages. The >> function's source code is just an opaque string. >> > > Oh, ouch, right. > > I think that this is one of those cases where it's better that we simply > advertise: BE AWARE OF THIS, rather than try to provide a half-baked > solution that gives the illusion of safety without the reality. Yes, but in the case of pluggable languages, you still load something that constitutes the "source". In the case of PL/Java, the jar for example. Whenever it changes, the stuff that depends on it up the chain is invalidated/rechecked/whatever. When I have a table that a view references, it tracks that. When I have a function that a index references, it kind of tracks that (I can't drop it because of the dependency, but I can replace it - with no warning/error btw). It just seems a worthy goal to aspire to... The cases about taking a string and sending it via execute don't seem to fit here for 1) cases where it is impossible to track the dependencies can be trivially constructed and 2) the very nature of the execute statement makes it obvious that it I shouldn't expect it to be tracked. Poor Analogy: In C, if foo calls bar and I remove bar I expect the compiler to tell me. If elsewhere in my code, I construct a memory address of where I believe bar should be and execute it I have no expectations for the compiler to tell me bar was removed.
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 7:04 PM, Matthew Dennis wrote: The plpgsql execute statement, as I understand it, means "take this string and execute like a client sent it to you". Of course, the string could come from anywhere. There's no inherent reason that I can think of (except good taste) that you could not write a function that retrieved a string out of a field in a table, executed it, and returned that as a value for use in an index. The client didn't send the string along, but it still creates dependencies. Anyway, as was correctly pointed out, tracking dependencies in PL/ pgSQL is bad enough, but PL/Java, PL/Perl... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Thu, Aug 28, 2008 at 8:15 PM, Christophe <[EMAIL PROTECTED]> wrote: > > On Aug 28, 2008, at 6:10 PM, Matthew Dennis wrote: > >> I'm not sure I follow. Couldn't you track which statements were prepared >> that called a function and either reprepare (just like reindex, recheck, >> etc) or in the case of dropping a function, refuse to drop it because >> something depends on it? >> > > EXECUTE in PL/pgSQL accepts a string and executes it as a statement. > (That's different from the SQL-level EXECUTE.) I'm not sure how one would > track dependencies there. Yes, sorry about that - it should have been obvious the execute you were talking about from the context. In any case, you don't track dependencies there for the same reason you don't track what SQL my application sends. The plpgsql execute statement, as I understand it, means "take this string and execute like a client sent it to you". It's explicit in the definition/description/documentation of the function that you aren't executing a "static" thing that would have dependencies you would track. However, when you used execute, you should get no different repsonse than what a client would get under the same circumstances.
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 6:51 PM, Alvaro Herrera wrote: Also, you have to keep in mind that we support pluggable languages. The function's source code is just an opaque string. Oh, ouch, right. I think that this is one of those cases where it's better that we simply advertise: BE AWARE OF THIS, rather than try to provide a half- baked solution that gives the illusion of safety without the reality. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
Christophe wrote: > > On Aug 28, 2008, at 6:10 PM, Matthew Dennis wrote: >> I'm not sure I follow. Couldn't you track which statements were >> prepared that called a function and either reprepare (just like >> reindex, recheck, etc) or in the case of dropping a function, refuse to >> drop it because something depends on it? > > EXECUTE in PL/pgSQL accepts a string and executes it as a statement. > (That's different from the SQL-level EXECUTE.) I'm not sure how one > would track dependencies there. There's no way at all in the general case (a function name could be passed as a parameter, for example). I think Matthew is suggesting to track dependencies at run time, but that seems a recipe for burnt fingers and an overall performance loss, for what seems a dubious gain. Also, you have to keep in mind that we support pluggable languages. The function's source code is just an opaque string. It could be anything. I doubt anyone uses PL/LOLCODE functions in production, but PL/Java functions are more likely, and for these we only have a class and method name, and little else. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 6:10 PM, Matthew Dennis wrote: I'm not sure I follow. Couldn't you track which statements were prepared that called a function and either reprepare (just like reindex, recheck, etc) or in the case of dropping a function, refuse to drop it because something depends on it? EXECUTE in PL/pgSQL accepts a string and executes it as a statement. (That's different from the SQL-level EXECUTE.) I'm not sure how one would track dependencies there. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Thu, Aug 28, 2008 at 7:52 PM, Christophe <[EMAIL PROTECTED]> wrote: > > On Aug 28, 2008, at 5:49 PM, Matthew Dennis wrote: > >> Yes, I can see that would indeed be a problem. Are there future plans to >> start tracking such dependencies? It seems like it would be a good idea in >> general. >> > > I believe the EXECUTE statement would thwart such plans. I'm not sure I follow. Couldn't you track which statements were prepared that called a function and either reprepare (just like reindex, recheck, etc) or in the case of dropping a function, refuse to drop it because something depends on it? Until today, I was under the impression that PG did track such dependencies (because of the errors I get when trying to drop functions/views/tables when other things depend on it). It seems to me that functions, prepared statements, checks and indexes are no different. If they depend on something, PG should track the depenency.
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 5:49 PM, Matthew Dennis wrote: Yes, I can see that would indeed be a problem. Are there future plans to start tracking such dependencies? It seems like it would be a good idea in general. I believe the EXECUTE statement would thwart such plans. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Thu, Aug 28, 2008 at 5:32 PM, Christophe <[EMAIL PROTECTED]> wrote: > On Aug 28, 2008, at 3:21 PM, Matthew Dennis wrote: > >> I have no doubt that someone would complain about it, but I think it's >> better than the alternative. >> > > Determining if changing any function will cause an index to break is not a > straight-forward problem. I don't believe that PG right now keeps a graph > of which functions call which, so replacing a function deep in the logical > calling hierarchy could break the index as easily as one mentioned at the > top. > Yes, I can see that would indeed be a problem. Are there future plans to start tracking such dependencies? It seems like it would be a good idea in general.
Re: [GENERAL] indexes on functions and create or replace function
On Thu, Aug 28, 2008 at 6:22 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Matthew Dennis" <[EMAIL PROTECTED]> writes: > > On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > >> (Changing the behavior of an allegedly IMMUTABLE function has a number > >> of other pitfalls besides that one, btw.) > > > I'm interested in knowing what they are - could you point me in the right > > direction (I've read the docs on immutable, etc) or briefly discuss them > > here please? Thanks... > > The main one I can think of offhand is that a call of the function might > have been folded to a constant in some cached plan somewhere, and > there's no mechanism to cause that plan to get redone. (This might or > might not get fixed in 8.4 --- since the plan no longer contains any > reference at all to the function, it's not exactly trivial to fix.) > > Another thing that's sort of related to the OP's complaint is something > like a table CHECK constraint that calls a user-defined function. > If you alter the function, is the system supposed to run around and > re-verify that constraint on every row? (And if so, what's supposed to > happen on a failure?) We don't enforce any such thing at the moment. > > (In fact, putting the two concepts together, it's possible that > redefining a user function that's used in a UNIQUE index might mean that > the UNIQUE condition now fails ... what should happen then?) > Well, my expectation is that in the case of the check or unique index, that PG does try to do a unique reindex and it does go check all the values. Assuming that it fails one of them, it refuses to replace the function. Of course, like I suggested with the REINDEX / NOREINDEX options, you could also have RECHECK / NORECHECK options to say "trust me, I know what I'm doing" and require them to specify one or the other when replacing a function that has checks and/or indexes referencing it. I really don't see much of a difference between I have a table with no unique index / check and I create one versus I've changed one. If I was to create a new unique index on an existing table that had duplicate keys, PG would rightfully refuse to create it of course. In any case, that's good information to have - thank you. Another question though. Since I could potentially start transaction, drop indexes/checks, replace function, create indexes/checks, commit tranasaction could I deal with the case of the constant folding into the cached plan by flushing the entire cache in the same transaction? Is cache flushing transactional? The cases I have for this are infrequent in time and the overhead of reindexing things, rechecking checks/unique indexes already dwarf the performance lost to flushing the cache. On a related note, if I had a maintenence window where I can shutdown all DB access, make the referenced changes to the functions/indexes/caches/checks and restart PG - in your opinion, are there other likely problems to changing an immutable function under those circumstances, or should that be pretty safe? In other words, I have a function that has indexes on it that does the wrong thing - what do I do to replace it?
Re: [GENERAL] indexes on functions and create or replace function
"Matthew Dennis" <[EMAIL PROTECTED]> writes: > On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <[EMAIL PROTECTED]> wrote: >> (Changing the behavior of an allegedly IMMUTABLE function has a number >> of other pitfalls besides that one, btw.) > I'm interested in knowing what they are - could you point me in the right > direction (I've read the docs on immutable, etc) or briefly discuss them > here please? Thanks... The main one I can think of offhand is that a call of the function might have been folded to a constant in some cached plan somewhere, and there's no mechanism to cause that plan to get redone. (This might or might not get fixed in 8.4 --- since the plan no longer contains any reference at all to the function, it's not exactly trivial to fix.) Another thing that's sort of related to the OP's complaint is something like a table CHECK constraint that calls a user-defined function. If you alter the function, is the system supposed to run around and re-verify that constraint on every row? (And if so, what's supposed to happen on a failure?) We don't enforce any such thing at the moment. (In fact, putting the two concepts together, it's possible that redefining a user function that's used in a UNIQUE index might mean that the UNIQUE condition now fails ... what should happen then?) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 3:21 PM, Matthew Dennis wrote: I have no doubt that someone would complain about it, but I think it's better than the alternative. Determining if changing any function will cause an index to break is not a straight-forward problem. I don't believe that PG right now keeps a graph of which functions call which, so replacing a function deep in the logical calling hierarchy could break the index as easily as one mentioned at the top. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > (Changing the behavior of an allegedly IMMUTABLE function has a number > of other pitfalls besides that one, btw.) > I'm interested in knowing what they are - could you point me in the right direction (I've read the docs on immutable, etc) or briefly discuss them here please? Thanks...
Re: [GENERAL] indexes on functions and create or replace function
On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > If it did that, you (or someone) would complain about the enormous > overhead imposed on trivial updates of the function. Since determining > whether the function actually did change behavior is Turing-complete, > we can't realistically try to determine that in software. So we leave > it up to the user to reindex if he makes a behavioral change in an > indexed function. > I have no doubt that someone would complain about it, but I think it's better than the alternative. I received no errors, no warnings and no indication whatsoever that my queries were now returning incorrect results. If people are worried about the case of changing the text/implementation of the function but not the behavior and needing to avoid the reindexing overhead, it should prevent you from doing it unless you explicitly say no reindexing is required. How about having NOREINDEX and/or REINDEX options in the create or replace function syntax? If no indexes depend on the function, no option is required. If there are indexes on the function, require one or the other (thus handling all cases). I have two main issues I'm trying to address: 1) Future queries not returning matching rows. 2) Having to manually go find all the indexes that use that function, drop them, replace the function, recreate them. This just seems like tedioum that really should live interal to PG.
Re: [GENERAL] indexes on functions and create or replace function
Tom Lane <[EMAIL PROTECTED]> writes: > Since determining whether the function actually did change behavior is > Turing-complete, we can't realistically try to determine that in software. > So we leave it up to the user to reindex if he makes a behavioral change in > an indexed function. Another option might be to mark the index invalid. Then allow the user to either reindex the index to enable it or use a magic DBA-only command to enable it asserting that a rebuild isn't necessary. Then of course someone would complain about the downtime caused by queries not using the index during the unavoidable window when the index is invalid. I'm not sure how to solve that. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
"Matthew Dennis" <[EMAIL PROTECTED]> writes: > Given table T(c1 int) and function F(arg int) create an index on T using > F(c1). It appears that if you execute "create or replace function F" and > provide a different implementation that the index still contains the results > from the original implementation, thus if you execute something like "select > * from T where F(c1)" after replacing the function that it now misses rows > that should be returned. In other words, the index isn't aware the function > is now returning different values. That's not the correct/expected behavior > is it? I would have expected that replacing the function would have caused > any indexes that depend on that function to be reindexed/recreated with the > new function implementation. If it did that, you (or someone) would complain about the enormous overhead imposed on trivial updates of the function. Since determining whether the function actually did change behavior is Turing-complete, we can't realistically try to determine that in software. So we leave it up to the user to reindex if he makes a behavioral change in an indexed function. (Changing the behavior of an allegedly IMMUTABLE function has a number of other pitfalls besides that one, btw.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] indexes on functions and create or replace function
Given table T(c1 int) and function F(arg int) create an index on T using F(c1). It appears that if you execute "create or replace function F" and provide a different implementation that the index still contains the results from the original implementation, thus if you execute something like "select * from T where F(c1)" after replacing the function that it now misses rows that should be returned. In other words, the index isn't aware the function is now returning different values. That's not the correct/expected behavior is it? I would have expected that replacing the function would have caused any indexes that depend on that function to be reindexed/recreated with the new function implementation.
Re: [GENERAL] Indexes & Primary Keys (based on the same columns)
On Mon, 2007-10-22 at 08:20 -0400, Bill Moran wrote: > In response to "Joshua D. Drake" <[EMAIL PROTECTED]>: > > > Ow Mun Heng wrote: > > > I'm wondering if what I'm doing is redundant. > > > > > > I have a primary key on columns (A,B,C,D) > > > and I've also defined an index based on the same columns (A,B,C,D) > > > > > > and sometimes in the query explain, I see the pkey being used for the > > > scan instead of the index. > > > > > > So.. That made me think perhaps the additional index on the _same_ > > > parameter is redundant. > > > > A primary key creates an index so having a second index with the same > > definition is redundant. > > Note the "same definition." > > Since this is a multi-column index, there may be some advantage gained > by having indexes defined slightly differently. I.e., your PK is > (ABCD) but you have an additional index on (DCBA) > > Whether or not this is actually helpful depends on the nature of the > queries you run. > I found that that might not matter as much as there are bitmap indexes which seems to be able to handle these. ---(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: [GENERAL] Indexes & Primary Keys (based on the same columns)
In response to "Joshua D. Drake" <[EMAIL PROTECTED]>: > Ow Mun Heng wrote: > > I'm wondering if what I'm doing is redundant. > > > > I have a primary key on columns (A,B,C,D) > > and I've also defined an index based on the same columns (A,B,C,D) > > > > and sometimes in the query explain, I see the pkey being used for the > > scan instead of the index. > > > > So.. That made me think perhaps the additional index on the _same_ > > parameter is redundant. > > A primary key creates an index so having a second index with the same > definition is redundant. Note the "same definition." Since this is a multi-column index, there may be some advantage gained by having indexes defined slightly differently. I.e., your PK is (ABCD) but you have an additional index on (DCBA) Whether or not this is actually helpful depends on the nature of the queries you run. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Indexes & Primary Keys (based on the same columns)
Ow Mun Heng <[EMAIL PROTECTED]> writes: > I'm wondering if what I'm doing is redundant. > I have a primary key on columns (A,B,C,D) > and I've also defined an index based on the same columns (A,B,C,D) Yup, 100% redundant. regards, tom lane ---(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: [GENERAL] Indexes & Primary Keys (based on the same columns)
On Sun, 2007-10-21 at 20:49 -0700, Joshua D. Drake wrote: > Ow Mun Heng wrote: > > I'm wondering if what I'm doing is redundant. > > > > I have a primary key on columns (A,B,C,D) > > and I've also defined an index based on the same columns (A,B,C,D) > > > > and sometimes in the query explain, I see the pkey being used for the > > scan instead of the index. > > > > So.. That made me think perhaps the additional index on the _same_ > > parameter is redundant. > > A primary key creates an index so having a second index with the same > definition is redundant. Many thanks for the confirmation. I'm dropping them... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Indexes & Primary Keys (based on the same columns)
Ow Mun Heng wrote: I'm wondering if what I'm doing is redundant. I have a primary key on columns (A,B,C,D) and I've also defined an index based on the same columns (A,B,C,D) and sometimes in the query explain, I see the pkey being used for the scan instead of the index. So.. That made me think perhaps the additional index on the _same_ parameter is redundant. A primary key creates an index so having a second index with the same definition is redundant. Appreciate comments. ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Indexes & Primary Keys (based on the same columns)
I'm wondering if what I'm doing is redundant. I have a primary key on columns (A,B,C,D) and I've also defined an index based on the same columns (A,B,C,D) and sometimes in the query explain, I see the pkey being used for the scan instead of the index. So.. That made me think perhaps the additional index on the _same_ parameter is redundant. Appreciate comments. ---(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
[GENERAL] indexes, and tables within tables
I was reading an interview with Chris Date the other day, which got me thinking about a problem I'm currently having: I have an application that keeps information in 6 denormalized tables, D1 through D6. To tie things together, all these tables have a common column, let's call it obj_id. There is an additional table P, that is generated from a query that uses D1 through D3 and JOINS them to another table, X. P also has this obj_id column. All these tables are pretty big, so I keep them indexed by obj_id. Table P in particular is a huge time series which I also need to keep indexed by date, so there is an index on P on (obj_id, date). Now, my problem: I need to keep P indexed, because I need to use it in queries very often, and I need them to be quick. However, due to the indexing, putting new rows in P takes forever, which it didn't used to when it wasn't indexed. I know that the typical solution is to drop the index, import the new data, and create the index again. However, this doesn't work for me, as the application is pretty interactive and users could be querying P and adding rows to it (on different obj_id's) simultaneously. The ideal situation would be if reindexing didn't reindex the whole table, but just those entries added with the given obj_id. This, as far as I know, is not possible. I don't really need table-wide indexes. The data in different obj_id's is not compared very often - what happens all the time is the cross-table relationships on the same obj_id. So what Date seems to wish for RDBMs to handle objects would work here: I would have a table of obj_id's, with columns D1 .. D6 and P. The object stored in a (row, column) would be a table with the appropriate entries, and in the case of P, an index on date. Retrieving all the related entries for a given obj_id would be trivial, as would inserting a new table object into the P column, for a given obj_id. Are there plans to handle table objects as possible values in other tables, as Date seems to propose? Is there currently a way of easing the design problem here? Any advice on redesigning the data? Thanks Jaime *** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. *** ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] indexes across multiple tables
On 2/18/07, Chris <[EMAIL PROTECTED]> wrote:
Toby Tremayne wrote:
> Hi all,
>
> I'm just experimenting with tsearch2 - I have it all working fine but I
> was wondering if there's a way to create indexes containing vector
> columns from multiple tables? Or if not, how do people usually manage
> this kind of issue?
Postgres doesn't support multi-table indexes so there's no way tsearch2
would be able to.
What exactly are you trying to achieve?
--
Probably best to join the tables, then simply do the search in the
WHERE clause. Something like
select p.partname, s.subassemblyname
from part p join subassembly s
on p.partid = s.partid
where p.partidx @@ to_tsquery('Some happy string')
or s.subidx @@ to_tsquery('Some happy string')
That's how I do it, anyway...
- Ian
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] indexes across multiple tables
Toby Tremayne wrote: Hi all, I'm just experimenting with tsearch2 - I have it all working fine but I was wondering if there's a way to create indexes containing vector columns from multiple tables? Or if not, how do people usually manage this kind of issue? Postgres doesn't support multi-table indexes so there's no way tsearch2 would be able to. What exactly are you trying to achieve? -- Postgresql & php tutorials http://www.designmagick.com/ ---(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
[GENERAL] indexes across multiple tables
Hi all, I'm just experimenting with tsearch2 - I have it all working fine but I was wondering if there's a way to create indexes containing vector columns from multiple tables? Or if not, how do people usually manage this kind of issue? Toby --- Life is poetry, write it in your own words --- Toby Tremayne Senior Technical Consultant Lyricist Software 0416 048 090 ICQ: 13107913
Re: [GENERAL] Indexes on array columns
I have tried testing the perofmance on indexing array element using standard
btree and it doesn't help anything. It still costing alot.
create index idx_properties_address_4 on properties ((address_arr[4]))
does contrib/intarray effective for text array?
On 3/16/06, Oleg Bartunov wrote:
contrib/intarray might help you
Oleg
On Wed, 15 Mar 2006, [EMAIL PROTECTED] wrote:
> On Wed, Mar 15, 2006 at 09:36:04AM -0700, Brendan Duddridge wrote:
>> Hi,
>>
>> Is it possible to put an index on an array column?
>
> Apparently yes (I just did it as a test). However, consider the
> following from the manual.
>
> Tip: Arrays are not sets; searching for specific array elements
> may be a sign of database misdesign. Consider using a separate
> table with a row for each item that would be an array element. This
> will be easier to search, and is likely to scale up better to large
> numbers of elements.[1]
>
> Arrays are attractive, but it seems they most often aren't the best
> solution. FOr instance, I beleive I read somewhere that the index
> will be on the whole array, and the individual elements are not
> indexed, so
>
> WHERE ary = {foo,bar}
>
> might benefit from your index, but
>
> WHERE 'foo' = ANY(ary)
>
> probably wouldn't. At least that's the impression I got from
> reading the archives of this list. I haven't done any testing of
> it.
>
>
> -karl
>
>
>
> 1. http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491
>
>>
>> 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 6: explain analyze is your friend
>
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Indexes and Inheritance
Keary Suska wrote:
Thanks to Erik, Jeff, & Richard for their help.
I have a further inheritance question: do child tables inherit the indexes
created on parent columns, or do they need to be specified separately for
each child table? I.e., created via CREATE INDEX.
I assume at least that the implicit index created by a primary key would
inherit, but I don't know if that assumption is safe.
In addition to what the others have replied, this is how i was told to
handle this (from this list):
-- create your parent table
CREATE TABLE parent_table (
id SERIAL PRIMARY KEY,
thisVARCHAR(64) NOT NULL,
thatVARCHAR(4) NOT NULL
);
-- create your child table(s)
CREATE TABLE child_table (
foo VARCHAR(64) NOT NULL,
bar VARCHAR(4) NOT NULL
) INHERITS (parent_table);
-- set the child table's id (from the parent) to take
-- the next value of the parent's SERIAL
ALTER TABLE child_table ALTER COLUMN id SET DEFAULT
nextval('parent_table_id_seq');
-- now create an index on that (so that you have as many indexes
-- on the parent's SERIAL as child tables)
CREATE UNIQUE INDEX child_table_pk ON child_table (id);
Do those last two for each child table and then make sure that you
perform your INSERTs on the child table(s).
brian
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Indexes and Inheritance
Erik Jones <[EMAIL PROTECTED]> writes: > No. In addition, child tables do not inherit primary keys. Think of it > like this: if you did a \d to describe a table that you were going to > use as a parent table in an inheritance chain, the child table would get > everything in the table listing the columns but nothing beneath the table. Not quite. CHECK constraints (and NOT NULL ones too) will be inherited. The main reason we don't yet inherit indexes/unique constraints is that the uniqueness would only be per-table, which is not what you'd expect. Eventually someone will think of a way to fix that ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Indexes and Inheritance
Keary Suska wrote: Thanks to Erik, Jeff, & Richard for their help. I have a further inheritance question: do child tables inherit the indexes created on parent columns, or do they need to be specified separately for each child table? I.e., created via CREATE INDEX. I assume at least that the implicit index created by a primary key would inherit, but I don't know if that assumption is safe. Thanks, Keary Suska Esoteritech, Inc. "Demystifying technology for your home or business" ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq No. In addition, child tables do not inherit primary keys. Think of it like this: if you did a \d to describe a table that you were going to use as a parent table in an inheritance chain, the child table would get everything in the table listing the columns but nothing beneath the table. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(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
[GENERAL] Indexes and Inheritance
Thanks to Erik, Jeff, & Richard for their help. I have a further inheritance question: do child tables inherit the indexes created on parent columns, or do they need to be specified separately for each child table? I.e., created via CREATE INDEX. I assume at least that the implicit index created by a primary key would inherit, but I don't know if that assumption is safe. Thanks, Keary Suska Esoteritech, Inc. "Demystifying technology for your home or business" ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
