Oracle index analyze stats – managed by whom?

a question about managing indexes.

In what cases do indexes stop being analyzed?

What’s the way to get indexes analyzed automatically?

Do I still need to manage index analysis somehow manually if they break? How can they break? How do I know they are broken? How to fix? In what intervals?

Perhaps there’s no auto-management? This would be very surprising. But if this is the case, how to manage indexes manually? Thanks.

Go to Source
Author: AlikElzin-kilaka

Postgres not using index in citext column

The following query is returning a SEQ Scan instead a Index.

select file_name from myschemadb.my_files where file_name = 'djsaidjasdjoasdjoaidad'

Engine

Postgres 11.5

My table:

CREATE TABLE myschemadb.my_files

    id int4 NOT NULL,
    file_name myschemadb.citext NOT NULL,
    status_id int4 NOT NULL,<br />
    file_key myschemadb.citext NOT NULL,
    is_fine bool NOT NULL DEFAULT true,
    create_date timestamptz NOT NULL DEFAULT now(),
    update_date timestamptz NULL,
    CONSTRAINT pk_my_files PRIMARY KEY (id)
);

The created index:

CREATE INDEX my_files_file_name_idx ON myschemadb.my_files USING btree (file_name);

Execution Plan

[
   {
      "Plan": {
         "Node Type": "Gather",
         "Parallel Aware": false,
         "Startup Cost": 1000,
         "Total Cost": 70105.63,
         "Plan Rows": 1,
         "Plan Width": 41,
         "Actual Startup Time": 109.537,
         "Actual Total Time": 110.638,
         "Actual Rows": 0,
         "Actual Loops": 1,
         "Output": [
            "file_name"
         ],
         "Workers Planned": 2,
         "Workers Launched": 2,
         "Single Copy": false,
         "Shared Hit Blocks": 58326,
         "Shared Read Blocks": 0,
         "Shared Dirtied Blocks": 0,
         "Shared Written Blocks": 0,
         "Local Hit Blocks": 0,
         "Local Read Blocks": 0,
         "Local Dirtied Blocks": 0,
         "Local Written Blocks": 0,
         "Temp Read Blocks": 0,
         "Temp Written Blocks": 0,
         "I/O Read Time": 0,
         "I/O Write Time": 0,
         "Plans": [
            {
               "Node Type": "Seq Scan",
               "Parent Relationship": "Outer",
               "Parallel Aware": true,
               "Relation Name": "my_files",
               "Schema": "myschemadb",
               "Alias": "my_files",
               "Startup Cost": 0,
               "Total Cost": 69105.53,
               "Plan Rows": 1,
               "Plan Width": 41,
               "Actual Startup Time": 107.42,
               "Actual Total Time": 107.42,
               "Actual Rows": 0,
               "Actual Loops": 3,
               "Output": [
                  "file_name"
               ],
               "Filter": "((my_files.file_name)::text = 'djsaidjasdjoasdjoaidad'::text)",
               "Rows Removed by Filter": 690443,
               "Shared Hit Blocks": 58326,
               "Shared Read Blocks": 0,
               "Shared Dirtied Blocks": 0,
               "Shared Written Blocks": 0,
               "Local Hit Blocks": 0,
               "Local Read Blocks": 0,
               "Local Dirtied Blocks": 0,
               "Local Written Blocks": 0,
               "Temp Read Blocks": 0,
               "Temp Written Blocks": 0,
               "I/O Read Time": 0,
               "I/O Write Time": 0,
               "Workers": [
                  {
                     "Worker Number": 0,
                     "Actual Startup Time": 106.121,
                     "Actual Total Time": 106.121,
                     "Actual Rows": 0,
                     "Actual Loops": 1,
                     "Shared Hit Blocks": 15754,
                     "Shared Read Blocks": 0,
                     "Shared Dirtied Blocks": 0,
                     "Shared Written Blocks": 0,
                     "Local Hit Blocks": 0,
                     "Local Read Blocks": 0,
                     "Local Dirtied Blocks": 0,
                     "Local Written Blocks": 0,
                     "Temp Read Blocks": 0,
                     "Temp Written Blocks": 0,
                     "I/O Read Time": 0,
                     "I/O Write Time": 0
                  },
                  {
                     "Worker Number": 1,
                     "Actual Startup Time": 106.821,
                     "Actual Total Time": 106.821,
                     "Actual Rows": 0,
                     "Actual Loops": 1,
                     "Shared Hit Blocks": 26303,
                     "Shared Read Blocks": 0,
                     "Shared Dirtied Blocks": 0,
                     "Shared Written Blocks": 0,
                     "Local Hit Blocks": 0,
                     "Local Read Blocks": 0,
                     "Local Dirtied Blocks": 0,
                     "Local Written Blocks": 0,
                     "Temp Read Blocks": 0,
                     "Temp Written Blocks": 0,
                     "I/O Read Time": 0,
                     "I/O Write Time": 0
                  }
               ]
            }
         ]
      },
      "Planning Time": 0.034,
      "Triggers": [],
      "Execution Time": 110.652
   }
]

I guess the problem is here:

"Filter": "((my_files.file_name)::text = 'djsaidjasdjoasdjoaidad'::text)",

This implicit conversion can be a problem.
But when i make a explicit conversion doesnt work too:

select file_name from myschemadb.file_history where file_name = 'djsaidjasdjoasdjoaidad'::myschemadb.citext

I see this link: Why does a comparison between CITEXT and TEXT fail?

but didn’t help me..

Go to Source
Author: Daniel1791

Composite multicolumn index for geopoint range and numeric range query

I am building an app where the server needs to select rows based on some criteria/filters. One of them is the location of the user and the radius at which the user want’s to see posts and other filters such date range and filter for a value of another column. This is going to be for an ad-hoc event discovery app.

I have read about PostGIS, its geometry,geography types and I know there is a native point datatype. Based on this answer I understood that it is better to order from equality to range columns, even though I feel like geo point column should be the first.

Suppose the following few rows of a simplified events table (disregard the validity position data):

id  event_title                  event_position   event_type  is_public  start_date
    (varchar)                    (point lat/lon)  (smallint)  (boolean)  (timestamptz)
--  ---------------------------  ---------------  ---------   ---------  ----
 1  "John's Party"               (122,35)         0           0          2020-07-05
 2  "Revolution then Starbucks"  (123,30)         1           1          2020-07-06
 3  "Study for math exam"        (120,36)         2           1          2020-07-07
 4  "Party after exam"           (120,36)         1           1          2020-07-08
 5  "Hiking next to the city"    (95,40)          3           1          2020-07-09
 6  "Football match"             (-42,31)         4           1          2020-07-10

Imagine the table contains several thousand records at least, obviously not only 6.

So in this table a user would be able to query public events close to (122,34) by 100km (suppose first three rows fall into this area) and of event types 0, 1 or 2 falling between dates 2020-07-05 and 2020-07-07. The user would get the rows with ID 2 and 3.

This is the query I want to optimize with an appropriate index. My question is, how is it possible to create such an index? I thought about GiST or GIN index but not sure how these could help. Thanks!

Go to Source
Author: Najib