As we known, there'is a command called "force index" within mysql database. So this can force mysql to use an index whether optimizer like or not. And my confusion is that how to do the same thing on postgresql? Any reply will be appreciated.
92788
How do I force Postgres to use an index when it would otherwise insist on doing a sequential scan?
81999
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.
35597
I'm setting up a database using phpMyAdmin. I have two tables (foo and bar), indexed on their primary keys. I am trying to create a relational table (foo_bar) between them, using their primary keys as foreign keys. I created these tables as MyISAM, but have since changed all three to InnoDB, because I read that MyISAM doesn't support foreign keys. All id fields are INT(11). When I choose the foo_bar table, click the &quot;relation view&quot; link, and try to set the FK columns to be database.foo.id and database.bar.id, it says &quot;No index defined!&quot; beside each column. What am I missing? Clarification/Update For the sake of simplicity, I want to keep using phpMyAdmin. I am currently using XAMPP, which is easy enough to let me focus on the PHP/CSS/Javascript, and it comes with phpMyAdmin. Also, although I haven't been able to set up explicit foreign keys yet, I do have a relational table and can perform joins like this: SELECT *  FROM foo  INNER JOIN foo_bar  ON foo.id = foo_bar.foo_id  INNER JOIN bar ON foo_bar.bar_id = bar.id;  It just makes me uncomfortable not to have the FKs explicitly defined in the database.
34540
Good evening,  I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed field without conditions is used.  SELECT * FROM table1 WHERE field1 NOT IN (SELECT index_field FROM table2)   When it comes to use conditions at table2, the query becomes realy slow on a large amount of data.  SELECT * FROM table1 WHERE     field1 NOT IN (         SELECT             index_field         FROM table2         WHERE             user_id = '2'     )   I was thinking about using LEFT Join but when I need to to the filtering for the user_id, it also becomes slow.  How may I solve this problem? Currently, I've no clue about that.  The original query looked sth. like this  SELECT     i.* FROM stream_item si LEFT JOIN user__publisher_item ui     ON ui.user_id = 2     &amp;&amp; ui.item_id = si.item_id INNER JOIN item i     ON i.id = si.item_id WHERE      si.stream_id IN (5,7,8,9,19,24,29,42,43,44)     &amp;&amp; ui.id IS NULL || (         ui.`read` = 0 || ui.saved = 1     ) GROUP BY     si.item_id ORDER BY     si.`found` DESC LIMIT 0,10     id      select_type     table   type        possible_keys                                                                           key                 key_len     ref                 rows        Extra 1       SIMPLE          si      index       PRIMARY,stream_2_item,stream_id_found                                                   stream_2_item       4           \N                  663236      Using temporary; Using filesort 1       SIMPLE          ui      eq_ref      user_id_item_id,user_2_item,user_id_read_saved_hidden,user_id_saved,user_id_hidden      user_id_item_id     8           const,si.item_id    1           Using where 1       SIMPLE          i       eq_ref      PRIMARY                                                                                 PRIMARY             4           si.item_id          1      I have a table which represents the n:m relationship between stream and items. An unique item can be accessible via multiple streams. Based upon this i have a state-table for user dependent states of an item. Currently, the user-state-entry will only be generated, if the item was read or saved the first tim. Otherwise, there is no entry in the user_item table. In the beginning, this query was fine but now, it takes a lot of time. Even if I leave out the "INNER JOIN" it takes 7 seconds on the current tables with around 500k rows each.  The table-structure in the background is as follows:   stream stream_item //n:m relation between stream and items item //unique items stream_group //user-defined groups of multiple streams stream_group_streams //_n:m relation between stream_groups and streams_ user user_item //state-table that holds the user-dependent states of an item - if there is no state, no row exists for an item   I already asked this question at  but added my 2 Edits with further information to late so that I haven't got an answer anymore.
22399
Is it possible to change the natural order of columns in Postgres 8.1?  I know that you shouldn't rely on column order - it's not essential to what I am doing - I only need it to make some auto-generated stuff come out in a way that is more pleasing, so that the field order matches all the way from pgadmin through the back end and out to the front end.
12741
When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?  In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default value - which could be very painful, obviously. With the optimization the DB would instantaneously create the new column, store just one copy of the default value which would be returned when no non-default value is found for that column in a suitable index data structure.  For example  such an .
123125
Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query.  SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category FROM Products p INNER JOIN ProductCategories c ON p.CategoryId = c.CategoryId WHERE c.CategoryId = 1;   When I create execution plan, table ProductCategories performs cluster index seek, which is as expectation. But for table Products, it performs cluster index scan, which make me doubt. Why FK does not help improve query performance?  So I have to create index on Products.CategoryId. When I create execution plan again, both tables perform index seek. And estimated subtree cost is reduced a lot.  My questions are:   Beside FK helps on relationship constraint, does it have any other usefulness? Does it improve query performance? Should I create index on all FK columns (liked Products.CategoryId) in all tables?
65698
What is the difference between postgres=# and postgres-#? I tried googling it and looking on the official site but could not get a hit for the symbols.
62411
Based on Paul White's , there are three optimization phases in SQL Server, search 0 (Transaction Processing), search 1 (quick plan) and search 2 (full optimization). Each phase has entry and termination conditions. My question is, can we force the optimizer to run at one optimization phase?
64730
--Setup, actual queries after the space --create table fltrind (a integer,b integer) truncate table fltrind DROP INDEX fltrind.nf DROP INDEX fltrind.f DECLARE @i integer SET @i=0 while @i&lt;100 BEGIN INSERT INTO fltrind(a) values(@i) SET @i=@i+1 END INSERT INTO fltrind(a,b) values(9000,0900)  insert into fltrind(a,b) select top 100000 f1.a,f1.b from fltrind f1 , fltrind f2  create nonclustered index nf on fltrind(b) INCLUDE(a) create nonclustered index f on fltrind(b) INCLUDE(a) where b is not null  UPDATE STATISTICS fltrind WITH FULLSCAN  select a from fltrind where b is not null  DROP INDEX fltrind.nf  UPDATE STATISTICS fltrind WITH FULLSCAN  select a from fltrind where b is not null   Looking at the query plan, the filtered index is not used as long as the non-filtered index is present.  Any idea why, and how can I get it to use the filtered index?  Dropping the non filtered index nf makes the optimizer use the filtered index f.  In fact, increasing the rowcount so that >10% of the rows qualify results in a table scan when the non filtered index is dropped.
86865
I have a table in mysql with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase the performance. Can anybody suggest some tips? Using 'innodb' as engine. Do I need to change it? I think its better for concurrent insert and select. pls give your suggestions. Do I need reindexing?  Thanks in advance..
115369
I am trying to accomplish the following in MySQL (see pseudo code)  SELECT DISTINCT gid FROM `gd` WHERE COUNT(*) &gt; 10 ORDER BY lastupdated DESC   Is there a way to do this without using a (SELECT...) in the WHERE clause because that would seem like a waste of resources.
17089
I want to search in all fields from all tables of a MySQL database a given string, possibly using syntax as:  SELECT * FROM * WHERE * LIKE '%stuff%'   Is it possible to do something like this?
51549
What's wrong with this query: INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;  It works without the WHERE clause. I've seemed to have forgot my SQL.
55749
Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Does PostgreSQL have the similar structure which records steps the optimizer takes?
13421
I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some other factor. I need all of the fields (or nearly all of them), so selecting just a subset isn't a quick fix. Selecting the fields that I want is still slow. Here's my table schema minus the names: integer                  | not null default nextval('core_page_id_seq'::regclass) character varying(255)   | not null character varying(64)    | not null text                     | default '{}'::text character varying(255)   |  integer                  | not null default 0 text                     | default '{}'::text text                     |  timestamp with time zone |  integer                  |  timestamp with time zone |  integer                  |   The size of the text field may be any size. But still, no more than a few kilobytes in the worst case. Questions  Is there anything about this that screams 'crazy inefficient'? Is there a way to measure page size at the Postgres command-line to help me debug this?
12656
How do you set a default value for a MySQL Datetime column?  In SQL Server it's getdate(). What is the equivalant for MySQL? I'm using MySQL 5.x if that is a factor.