Search type Search syntax
Tags [tag]
Exact "words here"
Author user:1234
user:me (yours)
Score score:3 (3+)
score:0 (none)
Answers answers:3 (3+)
answers:0 (none)
isaccepted:yes
hasaccepted:no
inquestion:1234
Views views:250
Sections title:apples
body:"apples oranges"
URL url:"*.example.com"
Favorites infavorites:mine
infavorites:1234
Status closed:yes
duplicate:no
migrated:no
wiki:no
Types is:question
is:answer
Exclude -[tag]
-apples
For more details on advanced search visit our help page
Results tagged with Search options user 2639

A database structure that can improve the speed of queries at the cost of disk space and slower inserts/updates. It stores a copy of one or more columns sorted but structures the data differently to allow faster access.

2
votes
upgrade script expects an index to exist only so that it can drop that index [...] Instead of taking the time to recreate the index is it possible to create a "dummy" or empty index with the same … name so that the upgrade script can run? Just delete that line from the update script. Or even better, use the IF EXISTS clause so it throws a notice not an error. Command: DROP INDEX
answered Nov 2 '17 by Evan Carroll
0
votes
You have two things you can look at it. The Mona Lisa. A museum attendant that is pointing to the Mona Lisa. You can't look at them both at the same time. Which do you want to look at it? That's t …
answered Jan 10 '17 by Evan Carroll
1
vote
My question is, with this partial index, wouldn't the database have to look up rows with processed not null (which is not fully indexed, so must be slow) before it gets to the indexed part of the … table (which in this case is processed itself!)? No, an index on IS NOT NULL has no need in itself to visit the table on queries restricted to IS NOT NULL or on queries which can be determined to be …
answered Sep 18 '17 by Evan Carroll
1
vote
Update This seems to be a bug with estimates. Bloom is using internally genericcostestimates. That's defeated if the seqscan goes parallel. Old attempt at answer You're not even using the index … table with parallel workers. Your indexing is totally irrelevant neither index is used (thus the <1% difference). Did you ANALYZE the table after you created the index? If so, try set enable_seqscan …
answered Feb 12 '19 by Evan Carroll
1
vote
2answers
The below is from Fundamentals of Database Systems (7th Edition) 17.1 Types of Single-Level Ordered Indexes There are several types of ordered indexes. A primary index is specified on the … field. If the ordering field is not a key field—that is, if numerous records in the file can have the same value for the ordering field—another type of index, called a clustering index, can be used …
asked Jan 12 '18 by Evan Carroll
2
votes
Why would it matter that the fillfactor is on a primary key? The index works the same way. If you UPDATE an indexed row to a point to a new version of the row -- that is an UPDATE that wasn't heap … -only, or if you add a row you have to potentially split the page the index is stored on to make room for the addition. The point at which you have to do that is when the index's page is full. The …
answered Oct 25 '17 by Evan Carroll
6
votes
1answer
I see numerous mentions online of Vodka indexing with regard to PostgreSQL. On the Wiki it's defined as, (Korotkov) Vodka maintains two index files, one for keys and one for heap item pointers … (postings). Each index file can be, in effect, managed by a different access method. This is a generalization of GIN, which uses btree (albeit its own btree code, not src/backend/access/nbtree) for …
asked May 25 '17 by Evan Carroll
1
vote
right question isn't Are all of those factors correct to create an index? But instead, For this given query, can I use an index? That is to say, go ahead and create the index. Then if the … index is not used in the query and you think it should be, you can come back here and paste the query, the schema, the result of EXPLAIN ANALYZE, and your configuration parameters and hope someone …
answered Jun 22 '18 by Evan Carroll
0
votes
I would suggest both, Making comment_id a PRIMARY KEY: which is both UNIQUE and NOT NULL If shop_name is frequently a part of the query, making shop_name it's own index. …
answered Feb 4 '18 by Evan Carroll
0
votes
Fixed Height Queries Tree (FHQT) "Metric-Temporal Access Methods" we can find the first definition, as a somewhat interesting aside, one of the primary authors on that paper is Andres Pascal himself …
answered Apr 11 '18 by Evan Carroll
2
votes
From High Performance MySQL Third Edition, Loose Index Scans MySQL has historically been unable to do loose index scans, which scan noncontiguous ranges of an index. MySQL’s index scans … generally require a defined start point and a defined end point in the index, even if only a few noncontiguous rows in the middle are really desired for the query. MySQL will scan the entire range of rows …
answered Apr 12 '18 by Evan Carroll
2
votes
1answer
Whenever I try to try to create a GIST index on text[], I get the above error. CREATE TABLE bar(f) AS VALUES (ARRAY['foo','bar','baz']); CREATE INDEX ON bar USING gist(f); ERROR: data type text … [] has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. I'm trying to create an Exclusion Constraint on bar, so I need a GIST index. …
asked Sep 6 '17 by Evan Carroll
0
votes
Using NOT EXISTS SELECT traffic_hit.created::DATE AS "group_by", COUNT(*) FILTER(WHERE traffic_hit.unique) AS "unique_visits", COUNT(*) AS "non_unique_visits" FROM traffic_hit AS "traffic_hit …
answered Aug 3 '17 by Evan Carroll
1
vote
As jjanes points out, I may not be right here if you've only got a GIN index. I'm waiting for the plan before I revise I don't believe the index "is no longer used" is correct. In the first query … , you're selecting ONLY the columns indexed. That means you can potentially use an "index-only scan". In the second query, you're selecting columns not in the index. That means you CAN NOT use an "index
answered Oct 18 '17 by Evan Carroll
0
votes
RDBMS-specific example if and only if they're the first columns then this will use the index [... otherwise] then the index will not be used, because matching records will be distributed all over … the index depending upon the value they have in column a. That's simply not true. In PostgreSQL for instance this is not applicable. Following the example you used, let say you had an index on (a …
answered Oct 23 '17 by Evan Carroll

15 30 50 per page